SQL SERVER – Introduction to JOINs – Basic of JOINs

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.
SQL SERVER - Introduction to JOINs - Basic of JOINs inner join

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.
SQL SERVER - Introduction to JOINs - Basic of JOINs left join

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.
SQL SERVER - Introduction to JOINs - Basic of JOINs right join

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.
SQL SERVER - Introduction to JOINs - Basic of JOINs outer join

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.

SQL SERVER - Introduction to JOINs - Basic of JOINs cross join - half

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

SQL SERVER - Introduction to JOINs - Basic of JOINs left join null

The above example can also be created using Right Outer Join.

SQL SERVER - Introduction to JOINs - Basic of JOINs right join null

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.

SQL SERVER - Introduction to JOINs - Basic of JOINs outer join null

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)

Best Practices, Database, SQL Joins, SQL Scripts, SQL User Group
Previous Post
SQL SERVER – FIX : ERROR : The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error:2147749896 (0×80041008)
Next Post
SQL SERVER – Check if Current Login is Part of Server Role Member

Related Posts

402 Comments. Leave new

  • sir ,in full outer join can we use this query to get result as same as your’s ?
    select * from table t1 full outer join table t2 on t1.colt2.col

    Reply
  • SELF-join is when two instances of the same table or view is joined together.

    Reply
  • Hi Pinal,

    I have a requirement where I am not able to achieve it. Please help.
    I am having 2 tables, one with 1000 records (Left Table) and other with 100 records (Right Table).
    When we try performing a Left outer join based on certain conditions, the output has more number of rows compared to the input 1000 records of the Left Table.
    I can understand that a Left outer Join can produce more number of records than the input.
    But our requirement is,
    1. The output should contain exactly same number of rows as that of the left table provided, whether the condition satisfies or not. But the condition has to be checked for sure.

    Thanks
    Dasarathy D R

    Reply
  • yashwanth K S
    May 24, 2017 7:32 pm

    its very usefull

    Thank you

    Regards
    Yashwanth K S

    Reply
  • Hi Pinal,

    Following is my query. I need to understand the difference between below two queries. Just assignment of fields is reversed.

    Query 1:

    SELECT t1.*,t2.*
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.ID = t2.ID

    Query 2:

    SELECT t1.*,t2.*
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t2.ID = t1.ID

    Which one is correct and why ?. Is there any performance or any other issue if we use second one.

    Reply
  • that’s useful article.

    Reply

Leave a Reply