The launch of Gandhinagar SQL Server User Group was a tremendous, astonishing success! It was overwhelming to see a large gathering of enthusiasts looking up to me (I was the Key Speaker) eager to enhance their knowledge and participate in some brainstorming discussions. Some members of User Group had requested me to write a simple article on JOINS elucidating its different types.
INNER JOIN
This join returns rows when there is at least one match in both the tables.
OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.
CROSS JOIN
This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.
Additional Notes related to JOIN:
The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.
SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO
The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
The above example can also be created using Right Outer Join.
NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.
You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.
USE AdventureWorks
GO
CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
CREATE TABLE table2
(ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
USE AdventureWorks
GO
/* INNER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* RIGHT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* OUTER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO
/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
/* CROSS JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
CROSS JOIN Table2 t2
GO
DROP TABLE table1
DROP TABLE table2
GO
I hope this article fulfills its purpose. I would like to have feedback from my blog readers. Please suggest me where do you all want me to take this article next.
Reference : Pinal Dave (https://blog.sqlauthority.com)
402 Comments. Leave new
superb
it is more helpful
who is tyro
Hi Pinal,
Need some information on Cartesian Product.
How much info do you need? Here’s plenty of it. Enough to blow off at least my head :)
https://en.wikipedia.org/wiki/Cartesian_product
https://en.wikipedia.org/wiki/Cartesian_join
GREAT WORK MY FRIEND…THIS ARTICLE HELPED ME TO KNOW JOINS CONCEPT VERY EASILY….CHEERS
Hello Pinal Dave,
I had a problem with the below query where I have used left outer join with where clause. It is not considering the where clause. Can u help me with this.
SELECT * FROM bill_cancellation left outer join bill_cancellation_details on bill_cancellation.reciept_no=bill_cancellation_details.reciept_no Where ((CONVERT(VARCHAR(10), date, 101) >= @todate) AND (CONVERT(VARCHAR(10), date, 101) <= @uptodate))
Thanks,
Minakshi.
Basic concepts very nicely explained
Hi Pinal Dave,
I had post this query before also. I had used this query to get the records between two given date. When I had used this where clause in normal query its working properly for same todate & uptodate it is working but in following case it is not working.
Can u help me with this.
SELECT * FROM bill_cancellation left outer join bill_cancellation_details on bill_cancellation.reciept_no=bill_cancellation_details.reciept_no Where ((CONVERT(VARCHAR(10),bill_cancellation. date, 101) >= @todate) AND (CONVERT(VARCHAR(10),bill_cancellation.date, 101) <= @uptodate))
Thanks,
Minakshi.
See if this works
SELECT * FROM bill_cancellation left outer join bill_cancellation_details on bill_cancellation.reciept_no=bill_cancellation_details.reciept_no
Where
bill_cancellation.date>= @todate
and
bill_cancellation.date, 101) < dateadd(day,1,@uptodate)
Thenx frnd
it really gud example and it works well
Hi,
I am truely thankful to u, for explaining the table joins in such a easy manner. to be very frank with u, i was very confused with the table join, but now i can say that table join is very easy. ‘
thanks a lot man
Can you explain more about joins with subquery and how to reduce the overhead when you joining something by subquery?
superb.
excellent article.
very easy to understand.
Hope u will give more articles.
Thanks
Hi,
It Seems Cross Join on 2 tables can be written in many ways. Here are some ways i found :
1)Select * from table1,table2
2)Select * from table1 t1
left outer join table2 t2 On t2.id = t2.id
3)Select t1.*,t2.* from table1 t1
inner join table2 t2 On (1=1)
Chill
Hi, the queries are simple and very gud.
i was very confused before going through this material.
now i m very confident in joins.
thank you very much.
Good article
Simple and usefull
Thanks
I was really looking for these join-explanations. Thanks alot!
I was really looking for these join-explanations……….give me more examples in real time ……….thank u a lot
Pinal,
Really you have framed a very nice article about the basic of joins and conveyed everything in a much easier way. Thanks a lot and keep up your good work.
Veera.
This is very useful for me,Thanks alot..
error ADO error Code: 0x80004005 Source Microsoft OLE DB Provider for SQL Server Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. SQL State: 08001 Native Error:17 Line 23171 Char: 4 Code: 0
How did you get this error?
Make sure you have right access to the server
Mr. Dave,
Very nicely done — thank you very much and keep up the great work!
Nice article Pinal. Especially the set diagrams which make very easy to understand. Your article reminds me one question which I use to ask in all interviews. I like to share the question;
SELECT tbl1.ObjId, tbl1.PersName, tbl2.PersAdd1, tbl2.PersAddr2
FROM dbo.tbl1
INNER JOIN
dbo.tbl2
ON tbl1.ObjId = tbl2.tbl1ObjId
Write a select statement, use LEFT OUTER JOIN instead of INNER JOIN which will produce the same result set.
Thanks,
Deb
select *
from Table1, table2
where table1.ID =table2.id
select *
from Table1
full outer join Table2 on (table1.ID=table2.ID )
where table1.ID is not null and table2.id is not null
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS not NULL