Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 25 June 2014

Types of Joins in SQL Server

A Visual Explanation of SQL Joins



Ref: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

INNER JOIN
FULL OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
CROSS JOIN

Assume we have the following two tables. Table A is on the left, and Table B is on the right. We'll populate them with four records each.
id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja
Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.




SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja
Inner join produces only the set of records that match in both Table A and Table B.

Venn diagram of SQL inner join

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader
Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

Venn diagram of SQL cartesian join

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null
Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

Venn diagram of SQL left join

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null
To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.

join-left-outer.png

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader
To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

join-outer.png

There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram:
SELECT * FROM TableA
CROSS JOIN TableB
This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. If you do the math, you can see why this is a very dangerous join to run against large tables.
QUIZ:
This Question was asked by interview.
Any one Can answer this question BY comment this blog?
 Write Query B shaded Area?

Ans:

SELECT * FROM TableA 
RIGHT OUTER JOIN TableB 
ON TableA.name = TableB.name 
WHERE TableA.id IS null

O/P:

ID     Name
1 Rutabaga
3 Darth Vader

Definition Of Joins:

  1. Left Outer Join.
  2. Right Outer Join.
  3. Full Outer Join.
Left Outer Join - A Left Outer Join in SQL Server returns the “Matched Rows”  from multiple tables and “Non Matched Rows” from Left side table. Follow the above picture for more understanding.
Right Outer Join - A Right Outer Join in SQL Server returns the “Matched Rows” from multiple tables and“Non Matched Rows” from Right side table. Follow the above picture for more understanding.
Full Outer Join - A Full Outer Join returns “Matched Rows” from multiple tables and also “Non Matched Rows” from multiple tables. Follow the above picture for more understanding.