Menu
This question was asked for MySQL already, but for Transact-SQL, what is the default JOIN behaviour?
That is, is simply writing JOIN in a query synonymous with writing INNER JOIN (as is the case with MySQL), or something else, like perhaps FULL OUTER JOIN?
778
SQL inner join It'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.
Common columns are columns that have the same name in both tables. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
Different Types of SQL JOINs (INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
INNER JOIN is the default if you don't specify the type when you use the word JOIN. You can also use LEFT OUTER JOIN or RIGHT OUTER JOIN, in which case the word OUTER is optional, or you can specify CROSS JOIN.
JOIN defaults to INNER JOIN behaviour.
To verify this, I ran the following code:
DECLARE @A TABLE (x INT) INSERT INTO @A SELECT 1 UNION ALL SELECT 2 DECLARE @B TABLE (x INT) INSERT INTO @B SELECT 2 UNION ALL SELECT 3 SELECT A.x AS 'A.x', B.x AS 'B.x' FROM @A A JOIN @B B ON A.x = B.x This produces just one row, consistent with INNER JOIN behaviour:
A.x | B.x -----+----- 2 | 2 Contrast this with a FULL OUTER JOIN:
... SELECT A.x AS 'A.x', B.x AS 'B.x' FROM @A A FULL OUTER JOIN @B B ON A.x = B.x This of course shows all three rows:
A.x | B.x -----+----- 1 | NULL 2 | 2 NULL | 3 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