Sunday, June 27, 2010

Explain SQL Server JOINs with examples?

I will explain types of SQL JOINs in in this article. JOINs in SQL Server can be classified as follows:

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

Each type is explained below with suitable examples:
Let’s consider two tables as Table1 & Table2 for our example.

– CREATE TEST TABLES
CREATE TABLE Table1(ID [int], Code [varchar](10));
GO
CREATE TABLE Table2(ID [int], Amount [int]);
GO


– INSERT DUMMY DATA
INSERT INTO Table1 VALUES
(1,’AAA’),(2,’BBB’),(3,’CCC’)
GO
INSERT INTO Table2 VALUES
(1,50),(2,30),(4,40)
GO

INNER JOIN:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Below is the query for inner join:

SELECT *
FROM Table1 A
INNER JOIN Table2 B
   ON A.ID = B.ID
GO

LEFT OUTER JOIN
Left Outer joins return all rows from the left table referenced with a left outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Left Outer Join:

SELECT *
FROM Table1 A
LEFT OUTER JOIN Table2 B
   ON A.ID = B.ID
GO

RIGHT OUTER JOIN
Right Outer joins return all rows from the right table referenced with a right outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Right Outer Join:

SELECT *
FROM Table1 A
RIGHT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

FULL OUTER JOIN
Full Outer joins return all rows from both the tables. Unmatched records will be NULL. Below is the query for Full Outer Join:

SELECT * FROM Table1 A
FULL OUTER JOIN Table2 B
ON A.ID = B.ID
GO

CROSS JOIN
In cross joins, each row from first table joins with all the rows of another table. If there are m rows from Table1 and n rows from Table2 then result set of these tables will have m*n rows. Below is the query for

SELECT * FROM Table1 A
CROSS JOIN Table2 B
GO

20 comments:

  1. Thanks fedef. I am happy to help you. BTW, it would have been nicer had you mentioned your actual name :)

    ReplyDelete
  2. Not forgetting the anti-semi-cross join!

    ReplyDelete
  3. very good, very easy to understand joins, and easy to remember, easy language very good

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. One of those most confusing matters related to SQL for me became cleaner with this...Thanks buddy ...

    ReplyDelete
  6. wow very nice article for understanding sql joins .......................my sql doubts is totally cleared ..

    thanks a lot...

    ReplyDelete
  7. really ncz .. keep up the gud wrk . :)

    ReplyDelete
  8. very useful to me...thanks a lot..

    ReplyDelete
  9. nice post...really thanks ,,,easy to understand

    ReplyDelete
  10. its a good solution for sql joins....Anyone can understand easily,,,,

    ReplyDelete
  11. really nice one anyone can understand easily

    ReplyDelete
  12. Hiii Guys – Today i am going to write – how we bind country,state,city in asp.net,C#,Sql server.

    In many cases, we need to bind hierarchical country – state – city like first select country than state will be bind on the basis of selected country and select state then bind city on the basis of selected state.

    You can use this below code -through copy paste else you can also download attached example here : -Download demo

    http://nirajtiwari.com/bind-countrystatecity-asp-netcsql-server/

    ReplyDelete
  13. I enjoying your post. You definitely have some great insight and great stories.

    Microsoft Server 2016
    Microsoft Server 2016 Repair

    ReplyDelete