Joining tables through reference integrity?

Question

Does it mean that this joining works like one to one relation?

Lest consider the following tables:

Table 1    Table 2
________   ________
id  name   id  name
1   O      1   T
2   B      1   N  

SELECT Table 1.name, Table 2.name FROM Table 1, Table 2 WHERE Table 1.id = Table 2.id

Will return this only last rows 1 N as joined rows?

1 O => 1N

How does it work if the second table has the same keys id. Does it take the last concurrences escaping previous?

It will give you 1 O => 1T and 1 O => 1N. Treat it as Cartesian product between {(1,0)} and {(1,T),(1,N)} — Akash KC, Oct 07 '17 at 22:49
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) — Bacon Bits, Oct 07 '17 at 22:57

score 1 · Accepted Answer · answered Oct 07 '17 at 22:51

1

No, joins will connect all matching rows, so your query will return 2 rows:

name1, name2
O    , T
O    , N

You can find a more generic description of various join types in the following SO question: What is the difference between "INNER JOIN" and "OUTER JOIN"?

answered Oct 07 '17 at 22:51

Shadow

33,525
10
51
64

Joins do not have the concept of 1:1 or 1:n. They connect matching records. – Shadow Oct 08 '17 at 01:14

score 0 · Answer 2 · answered Oct 07 '17 at 23:15

Never use commas in the FROM clause. Always use correct, explicit JOIN syntax. Your query should be written as:

SELECT t1.name, t2.name
FROM Table1 t1 JOIN
     Table2 t2
     ON t1.id = t2.id;

JOIN returns all matching rows. There is no concept of SQL of "first" or "last" rows. SQL tables represent unordered sets.

This is easy enough to check. Here is a SQL Fiddle demonstrating the results.

Paul Maxwell · Answer 3 · 2017-10-08T06:21:04.650

Just a note about "conventions" which may help you understand the "id" a little better.

"id" (by common convention) is an auto-numbered integer which may be used to uniquely locate a specific row in that table.

Every table (by common convention) will have its own "id" column, but this does not mean you always join by these columns.

Table2 is likely to contain a "foreign key" which enables a join to Table1

The naming of a "foreign key" column (by common convention) is often a combination of "table name" & underscore & "id". So a possible revision to your example would be this:

Table1     Table2
________   ________________________
id  name   id   table1_id  name
1   O      1000         1   T
2   B      9678         1   N

Note how the ID from Table1 has no relationship to ID of Table2 in this example and so the query would now be:

SELECT t1.name, t2.name
FROM Table1 t1 
INNER JOIN Table2 t2 ON t1.id = t2.table1_id;

Joins adhere to the logic specified, here that is t1.id must equal t2.table1_id. There is no "first" of "last" in that logic. Any rows that meet the specified condition are joined. (nb: & this could mean no rows get joined depending on circumstances)

Please don't use commas between table names after the word FROM. Get used to the terms such as INNER JOIN, LEFT OUTER JOIN (or their abbreviations JOIN and LEFT JOIN) which is just so much better in the long run.

Joining tables through reference integrity?

3 Answers3