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
Good article and very simple to understand ,but i think explaining it with table data would be more informative.
well,its a simple and more understanble article.I feel anybody can get basic information about JOINs.Explanation with quries is very good it was very useful for me in learning basics
Hi… Pinal Sir! nice techniques for understanding various joining techniques through coding and pictures.It helps me too much to understand joining.
Thanks…….
what is the difference between LEFT JOIN and LEFT OUTER JOIN ?
Both are same. The word OUTER is optional
ya its same ..there is no difference
Hi pinal ,
I need some help on this..
I am pursuing bscIT from Kuvempu University and
I want to become a DBA but where can i start this, i am so confuse which course is better for me and which institute is best for DBA can u plz suggest me on this matter
Plz help me
Hi Pinal,
I know there is no difference between Cross join without where clause and Cartesian product. Then why cross join is there at first place.
Because everything that cross join can be replaced by Cartesian product.
For example the below query:
Select *
from A a
cross join B b
where a.id = b.id
can be replace with
Select *
from A a, B b
where a.id = b.id
then what’s the point of having cross join ?
Thanks,
Manish
hi pinal,
the above detail abt join is very informative.
thanks for this knowledge.
Thank’s this tutorial help me lot…..
This is the best Article I have ever read on JOINS. I was always confused about this from long time(long time meaning from my engineering, now I’m working and its been 5 years. you can understand how I am :D). Thanks man.
Wow, super explanation. More useful information.
Good One, diagrams only cleared my concepts. Very useful for the learners. Thank U
Hi Pinal,
The diagramatic representation clears most of the doubts. Great article!!!
Can you tell something about Self Join, the scenarios in which Self-JOINS are useful?
If you store empid and manager id in the same table and you want to find out manager name for each employee, you need to use the self join
Hi,
The article is very nice and useful. I got clarity about the join concepts because of this article.
But I would like to request to explain joins with live examples or examples which will include a complete database and tables in it.
Or more practical examples which are actual used in programming or development of any application.
Great representation,,, Thanks a lot………
Hi Pinal,
the presentation is just awesome.made understanding of joins clearer..
thank you so much…
TNX 4 your grate explanation.it is so easy to understand.and helped me alot.
Hi Pinal,
I have been following your website and blog for a while and it’s been quite an eye opener especially for a noob like me.
I have an issue I think I can get my eyes open here.
I need to generate an report based on the database at my new work place and I need it to be in a compact query (if possible 1 select from multiple select statements).
The tables look like this:
clients
id last_name first_name
1 LN1 FN1
2 LN2 FN2
3 LN3 FN3
sales
id procedure nr_of_times
1 a 1
1 b 2
1 f 1
2 b 3
2 f 2
3 d 1
3 g 2
3 h 1
item
item_id description
a description1
b description2
c description3
d description4
e description5
f description6
g description7
h description8
i description9
I need to find out total unique buyers and how many of each article was sold for each item. The result should look like this.
results_table
item_id description total_buyers total_sold
a description1 1 1
b description2 2 5
c description3 0 0
d description4 1 1
e description5 0 0
f description6 2 3
g description7 1 2
h description8 1 1
i description9 0 0
Looking forward for your reply ;)
Please replace procedures from the sales table with item_id, number_of_times ibeing total times that item/service was purchased
@Biberon
hope this will work for your task
Please replace procedures from the sales table with item_id
with cte as
(
select itemid,sum(nr_of_times) as total ,count(id) as users
from sales
group by itemid
)
select t1.*,cte.users,cte.total
from item t1 left outer join cte
on t1.itemid=cte.itemid
this helped me really alot.