What’s the best way to join on the same table twice?

by

First, I would try and refactor these tables to get away from using phone numbers as natural keys. I am not a fan of natural keys and this is a great example why. Natural keys, especially things like phone numbers, can change and frequently so. Updating your database when that change happens will be a HUGE, error-prone headache. *

Method 1 as you describe it is your best bet though. It looks a bit terse due to the naming scheme and the short aliases but… aliasing is your friend when it comes to joining the same table multiple times or using subqueries etc.

I would just clean things up a bit:

SELECT t.PhoneNumber1, t.PhoneNumber2, 
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2
FROM Table1 t
JOIN Table2 t1 ON t1.PhoneNumber = t.PhoneNumber1
JOIN Table2 t2 ON t2.PhoneNumber = t.PhoneNumber2

What i did:

  • No need to specify INNER – it’s implied by the fact that you don’t specify LEFT or RIGHT
  • Don’t n-suffix your primary lookup table
  • N-Suffix the table aliases that you will use multiple times to make it obvious

*One way DBAs avoid the headaches of updating natural keys is to not specify primary keys and foreign key constraints which further compounds the issues with poor db design. I’ve actually seen this more often than not.

More Related Contents:

  1. Explicit vs implicit SQL joins
  2. SQL JOIN and different types of JOINs
  3. SQL update from one Table to another based on a ID match
  4. Does the join order matter in SQL?
  5. SQL Inner-join with 3 tables?
  6. What is the difference between JOIN and UNION?
  7. Left Outer Join Not Working?
  8. SQL left join vs multiple tables on FROM line?
  9. SQL Joins Vs SQL Subqueries (Performance)?
  10. SQLite – How do you join tables from different databases?
  11. Can we use join for two different database tables?
  12. Call a set-returning function with an array argument multiple times
  13. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  14. MySQL – Selecting data from multiple tables all with same structure but different data
  15. Record returned from function has columns concatenated
  16. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  17. ORA – 00933 confusion with inner join and “as”
  18. PostgreSQL JOIN with array type with array elements order, how to implement?
  19. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  20. Venn Diagram for Natural Join
  21. SQL query: Simulating an “AND” over several rows instead of sub-querying
  22. Why do multiple-table joins produce duplicate rows?
  23. SQL SELECT from multiple tables
  24. IN vs. JOIN with large rowsets
  25. SQL Inner Join On Null Values
  26. INNER JOIN vs multiple table names in “FROM” [duplicate]
  27. Is NATURAL (JOIN) considered harmful in production environment?
  28. Re-Writing “Fuzzy Join” Functions from R to SQL
  29. Removing duplicate rows (based on values from multiple columns) from SQL table
  30. Procedurally transform subquery into join

Leave a Comment