Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

For SQL, when did it start to be desirable to always use the words "Inner Join" instead of implicitly joinly?

For SQL, when did it start to be desirable to always use the words "Inner Join" instead of implicitly joining by:

select * from t1, t2 where t1.ID = t2.ID;

? Is it just for style or to distinguish between outer join or are there other reasons for it?

like image 935
nonopolarity Avatar asked Apr 24 '10 23:04

nonopolarity


People also ask

When should I use inner join in SQL?

You'll use INNER JOIN when you want to return only records having pair on both sides, and you'll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not.

Does SQL default to inner join?

SQL inner joinIt's the default SQL join you get when you use the join keyword by itself. The result of the SQL inner join includes rows from both the tables where the join conditions are met.

Why use inner join instead of join?

Generally, we use INNER JOIN when we want to select only rows that match an ON condition. If no rows match the ON condition, then it will not return any results. This can be somewhat stricter than using a LEFT JOIN .

Is inner join the default?

INNER is the default; LEFT, RIGHT, and FULL imply an outer join. The join condition is specified in the ON or USING clause, or implicitly by the word NATURAL. The join condition determines which rows from the two source tables are considered to "match", as explained in detail below.


1 Answers

The INNER and OUTER JOIN syntax was formalized in the SQL-92 specification. In many database products such as MySQL and SQL Server, you can omit the "INNER" word from inner joins and simply use "JOIN". Similarly, many database products let you omit the word "OUTER" and simply use "LEFT JOIN" or "RIGHT JOIN" for outer joins. The old outer join syntax of *= or =* created ambiguities in many circumstances. Many products have or very soon will stop supporting the old outer join syntax.

Prior to the SQL-92 specification, the vendors each used their own syntax indicated an outer join. I.e., *= was not universal (I seem to remember someone using ?=). In addition, they did not implement the outer join in a universal way. Take the following example:

Table1
Col1    Col2
1       Alice
2       Bob

Table2
Col1    Col2
1           1
2           2
3           3
4           4

Select 
From Table1, Table2
Where Table2.Col1 *= Table1.Col1

The above query would generally yield:

1   1   1       Alice
2   2   2       Bob
3   3   Null    Null
4   4   Null    Null

Now try:

Select 
From Table1, Table2
Where Table2.Col1 *= Table1.Col1
    And Table2.Name = 'Alice'

On some database products, you would get:

1   1   1       Alice

On others you would get:

1   1   1       Alice
2   2   Null    Null
3   3   Null    Null
4   4   Null    Null

In short, it is ambiguous as to whether the filtering on the unpreserved table should be applied before or after the join.

like image 106
Thomas Avatar answered Sep 23 '22 01:09

Thomas