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
Hi Pinal, Its really nice depiction. Could you pl extend this article to “Cross APPLY” and “Outer APPLY” as well..
@_sreekanth_
Fantastic Article. Excellent Work
fabulous and precious article. You spoon feed the sql in awesome manner.I never forgot sql again.
really nice artical………i like the way of your presentation……………..can u tell me how to connect the database to visual studio web applications ………………..plz
Good One………………
Dear Pinal sir,
i have a small problem of the following query but i m not getting the proper result ..my query like
create table T1(C1C1 varchar(10),C1C2 varchar(10))
insert into T1(C1C1,C1C2)
select ‘c11′,’c12′
union All
select’A11′,’A12’
create table T2(C2C1 varchar(10),C2C2 varchar(10))
insert into T2(C2C1,C2C2)
select ‘c21′,’c22′
union All
select’A21′,’A22’
create table T3(C3C1 varchar(10),C3C2 varchar(10))
insert into T3(C3C1,C3C2)
select ‘c31′,’c32′
union All
select’A31′,’A32’
i want the result in this format
C1C1 C2C1 C3C1
A11 A21 A31
plz reply ,i am waiting for the result
thanks in advance
select c1c1,c2c1,c3c1
from
(select * ,Row_number() over(order by c1c1) as RowSeqNo from t1)
as t1
join (select * ,Row_number() over(order by c2c1) as RowSeqNo from t2)
as t2
on(t1.RowSeqNo=t2.RowSeqNo)
join (select * ,Row_number() over(order by c3c1) as RowSeqNo from t3) as t3
on(t3.RowSeqNo=t2.RowSeqNo)
where t1.RowSeqNo=1
Thanks for the clean and easy to understand examples! Just what I needed.
Hello, nice diagrams, but I have a problem with Inner joins. Many people recommend them as faster than left outer and routinely use inner rather than an outer join. The diagram shows a good use, where the purpose is to identify a small set of rows that have a common link field. I think many people use inner joins in a situation where the two circles are almost completely overlapping. The assumption is that nothing will get left out. That may work well if you have complete confidence that it is impossible to have a record in one table without a match in the other, but NULL values in either table will cause data to disappear. If your database has referential integrity to ensure a match, then go for it. Transaction processing databases may be set up with triggers and transaction rollbacks to ensure this problem doesn’t occur. But there is a whole big world out there that has wilder data. I work with electronic medical record systems and find that NULL values are a constant headache. Someone either misunderstood that the field should be filled in or got distracted, or just didn’t want to take the time. A left outer join will tend to identify these situations so they can be corrected. An inner join will make them invisible and worse.. you will still probably get enough data returned by the query to be overly confident that is it correct and complete. It comes down to knowing your data/database. If you want to use an inner join between two tables, make sure there are no NULLs in the link field. Run a separate test, and remember you only proved it to be true today, tomorrow is another day. Thanks for hearing me out.
good article…………
nicely cleared concept of joins
Brilliant work… Awesome dude…
sir, i have 2 database table,one with 700 rows and other with 2000 rows.in both of tables,there is a primary key as column name serial no.Now what i have to do is,i have to make a webpage in which when we enter serial no in textbox,all the remaining enteris will shown automatically.I have done it for table with 700 rows,now i left with is when i enter the serial no,it should pick values from table with 2000 rows.so,how i shuold do it,please help me in the context.
Awesome article.. easy to understand the Joins Concept. Thank you…
mindblowing guideness
Superb article … Very simple to understand with lots of complicated information simplified.
Pinal Dave: I need a result similar to the NOT INNER JOIN example wherein I need all unique records from both tables, but not with the result id value | id value. I need them to combine into simply id value. Is this possible using a join? I am trying to avoid large deduping queries using a UNION.
A very sofisticated description……………….. THANKYOU
Excelente !!! Congrats !!
Thank you :)
it is excellent presentation