1
\$\begingroup\$

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?

\$\endgroup\$
1
\$\begingroup\$

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.
\$\endgroup\$
  • \$\begingroup\$ 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 ? \$\endgroup\$ – Adam May 27 '16 at 7:26
  • \$\begingroup\$ 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.) \$\endgroup\$ – 200_success May 27 '16 at 7:30
  • \$\begingroup\$ 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. \$\endgroup\$ – 200_success May 27 '16 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.