Get all members from country depending on multiple tables

Question

I have 4 tables, filled with some example data

user table

id | name | idAddress | 
------------------------
1 | Bernd | 1 | 
2 | Max | 2 |
3 | Tom | 3 |
4 | Bob | 4 |
5 | Alice | 5 |

address

id | country | zip |
----------------------
1 | DE | 39213 |
2 | CH | 13847 |
3 | NL | 38472 |
4 | DE | 94872 |
5 | MT | 54682 |
6 | US | 4682 |
7 | DE | 45853 |
8 | NA | 12144 |

orga2Address

idOrga | idAddress |
------------------
1 | 6 | 
1 | 7 |
2 | 7 |
2 | 8 |

orga2User

idUser | idOrga |
-----------------
1 | 1 | 
1 | 2 |
2 | 1 |
3 | 2 |
4 | 2 |

I want to select all user names that have address in 'DE' in the user table or that are in an orga which has an address in 'DE' in orga2Address.

I was able to get the result with the UNION command like this (see this SQLFiddle):

SELECT `name`, `country` FROM (SELECT `user`.`name`, `address`.`country` from `user` 
INNER JOIN `address` ON `user`.`idAddress` = `address`.`id`
UNION
SELECT `user`.`name` from `user` 
INNER JOIN `orga2User` `o2U` ON `o2U`.`idUser` = `user`.`id`
INNER JOIN `orga2Address` `o2A` ON `o2A`.`idOrga` = `o2U`.`idOrga` 
INNER JOIN `address` `a` ON `a`.`id` = `o2A`.`idAddress`) as `U` WHERE `U`.`country`="DE";

The result

name |
--------
Bernd |
Max |
Tom |
Bob |

Is there a quicker and better way to get the wanted result?

Answer 1

Here's a more compact formulation (SQL Fiddle):

SELECT DISTINCT user.name, address.country
 FROM orga2Address
 INNER JOIN orga2User
 ON orga2User.idOrga = orga2Address.idOrga
 RIGHT OUTER JOIN user
 ON user.id = orga2User.idUser
 INNER JOIN address
 ON address.id IN (user.idAddress, orga2Address.idAddress)
 WHERE address.country = 'DE';

The trick is, instead of UNION, perform a JOIN with more than one join path. (We unfortunately need to use DISTINCT, but that is no worse than your UNION, which also de-duplicates results.)

In addition, I suggest:

• formatting your code with better indentation. In this example, the SELECT statement has a FROM clause and a WHERE clause. The FROM clause has multiple JOINs, each of which has an ON clause.
• using single quotes for string literals like 'DE', which is standard ANSI SQL. Using double quotes for strings is a MySQLism, and it fails if SQL_MODE = 'ANSI_QUOTES' is in force.
• avoid overdoing the `quotes`. If you didn't need them in your CREATE TABLE statements, you don't need them in your query either.

• Adam: Thank you. Is your compact formulation also quicker, or just more readable? Would it improve the SQL if one would remove the WHERE clause and change ON address.id IN to ON address.id AND address.country = 'DE' IN ?
• 200_success: Definitely don't absorb the WHERE clause into the join condition — it's less readable. (With OUTER JOINs, the meaning would also change when you do that.)
• 200_success: Is it quicker? Probably not, and in fact could be slower if MySQL doesn't know how to handle such join conditions efficiently. Time it both ways yourself to see if it makes a difference for your data set.