Connection between tables

Question

I would like to know what is the best way to create a connection between two tables (or more). I have seen two different ways to do so.

In the next example, I have 2 tables. Table1 has customerId (key) and customerName. Table2 has customerId (key) and CustomerPhone

table1:

 customerId | customerName|  
============+=============+
 1          |  Josh       |  
 2          |  Nadia      |

table2:

 customerId | customerPhone|  
============+==============+
 1          |  123         |  
 2          |  456         |

Which query is the best and why:

SELECT Table1.customerId, Table2.customerPhone  
FROM   Table1, Table2  
WHERE  Table1.customerId = Table2.customerId

Query2:

 SELECT Table1.customerId, Table2.customerPhone  
 FROM   Table1   
 Inner Join  Table2 ON Table1.customerId = Table2.customerId

If the key is unique in both tables, why isn't the data being stored in a single table? — BenM, Jun 15 '20 at 14:07
Does this answer your question? [SQL left join vs multiple tables on FROM line?](https://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line) — Ahmed Sunny, Jun 15 '20 at 14:08
the data isn't being stored in a single table for the example. @BenM — stackoverflowuser50, Jun 15 '20 at 17:21

score 2 · Answer 1 · answered Jun 15 '20 at 14:08

2

The second option is more common and considered to be the right one, however they are both doing the same thing under the hood

answered Jun 15 '20 at 14:08

TomerRam

score 1 · Answer 2 · answered Jun 15 '20 at 14:09

1

The first query makes first a cross join and then reduces the result to fit the where cflause.

The second matches the tables by check ing if the condition in the ON clause ist met.

Both do the same, but the first is slowe

answered Jun 15 '20 at 14:09

nbk

The Impaler · Answer 3 · 2020-06-15T14:18:07.813

1

The first query is written using old syntax (SQL-89):

SELECT Table1.customerId, Table2.customerPhone  
FROM   Table1, Table2  
WHERE  Table1.customerId = Table2.customerId

The second query is written in modern syntax (SQL-92):

SELECT Table1.customerId, Table2.customerPhone  
FROM   Table1   
Inner Join  Table2 ON Table1.customerId = Table2.customerId

They are equivalent. Use SQL-92. Use SQL-92!

edited Jun 15 '20 at 14:18

answered Jun 15 '20 at 14:12

The Impaler

Why the downvote? Did I miss something? – The Impaler Jun 15 '20 at 14:19
I don't know why the downvote. thank you for your explanation. I would upvote, but I'm new and not able to do it yet. – stackoverflowuser50 Jun 15 '20 at 17:24

3 Answers3