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
can you help me
——————————————————–
I have a table namely “Table1”
Table1
Code Description
120537924442 TEST1
SQL
SELECT A.Code, ContactNo, Table1.Code, Description
From
(
SELECT (JobNo+’4’+’442′) AS Code, Table2.JobNo, Table2.ContactNo, Table1.Code, Table1.Description
FROM Table2
Where CategoryID=4 AND SubCategoryID=442
) A
LEFT OUTER JOIN Table1
ON Table1.Code=A.Code
I want to output like below:
Table2.Code ContactNo Table1.Code Description
130539914442 XXXXXXX NULL NULL
120537924442 YYYYYYY 120537924442 TEST1
250932584442 ZZZZZZZZ NULL NULL
But I am getting …..
Table2.Code ContactNo Table1.Code Description
130539914442 XXXXXXX NULL NULL
120537924442 YYYYYYY NULL NULL
250932584442 ZZZZZZZZ NULL NULL
nice
it simply great article
thanks
I want to know, is it possible to join four to five tables in a data base
and result should be different rows
Yes possible. Have you tried it?
sir can you provide the query. if it possible
simply awesome for beginers
Really These article Helpful for me , As QA person we are not very sure about working in SQL but most of my concepts are clear here , Thnaks Pinal and keep good work
really super explanation thank u sir,,
Great stuff!
Hi,
It’s Really helpful to me. Thanks for sharing the KT on SQL.
hi
Please solve the my problem
for table bind the join and use the one table in one column in one month many entry use the group by
i am all posible condition apply
my answer is not correct
please mail the my answer
please send the Email
Really helpful, Thank you so much
I need a sol for this:
I have a query which transfers records from one server to another server which is in NJ.The query is something like this..
use MYDBNAME
go
insert into [191.168.5.1].[HOSTEDDBANME].[dbo].[TABLE NAME]
select * from [MYDBNAME].[dbo].[TABLE NAME] with (nolock)
where TIMESTAMP BETWEEN ‘2012-07-23 00:00:00’ AND
‘2012-07-24 00:00:00’
For transferring 80000 records taking more than 14hrs.Is there any change in query which gives fast performance.Please help me.
what is the output of this
select count(distinct tbl1.a) as c from tbl1
inner join tbl2 0n tbl1.a=tbl2.a
left join tbl3 on tbl1.a=tbl2.a AND tbl2.b=tbl3.b
where
tbl1.a in(select a from tbl4 where tbl4.c=tbl3.c)
AND (tbl4.e=tbl3.e OR tbl4.f < tbl2.f)
reply must……..
Hello Sir,
I am working on a project where there is one Stored procedure containing many inner joins. Recently i added more logic to that stored procedure….
Now when i give input to that stored procedure i want to check whether the results are ok… Problem here is the database tables are new to me .. Please suggest…
This is one of the best understandable article from ever i read.
Please keep up like this article
nice article
Excellent description..
This is a great article PinalDave. One problem I am having is when I try to do a left join that is using a function such as sum I am not getting everything listed only where the sum is not null and a Left Outer Join is being used. For example,
Select e.EmpId, s.SaleDate, sum(s.Sales)
from Employee e
Left Outer Join Sales s on e.EmpId = s.EmpId
Where s.SaleDate between ‘8/1/2012’ and ‘8/10/2012’
Group by e.EmpId, s.SaleDate
Any way to make sure all Employees show up even if they didn’t sell anything for a day?
Thanks.
Just Simply Awsome……….
Good Article. Excellent Work