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?
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.
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.
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 .
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With