Post
Ask Question

Joins in SQL Server

What is SQL Joins?

Without joins, it is impossible to conceive how useful it would be to obtain data from many of the same locations. Joins are among the most crucial concepts in SQL Server. Real-world relational databases, like SQL Server, Oracle, MySQL, and others, are used to store our data in several logical tables that are connected by a common key value.

As a result, we frequently need to combine data from two or more tables to produce the necessary output in accordance with certain criteria. With the help of the SQL JOIN clause, we can easily obtain this kind of data in SQL Server.

We can collect data from two or more related tables and combine it into a useful result set by using the join clause. Using a SELECT statement and a join condition, we may join the table.

Types of JOINS in SQL Server

SQL Server mainly supports four types of JOINS

  1. INNER JOIN
  2. SELF JOIN
  3. CROSS JOIN
  4. OUTER JOIN

SQL Server Joins

Inner Join

This JOIN returns all data from several tables that meet the required join criteria. It operates as a default join and is the most straightforward and well-liked type of join. We will obtain the same results if we exclude the INNER term from the JOIN quey.

  • The following visual representation explains how INNER JOIN returns the matching records from table1 and table2:
  • The following visual representation explains how INNER JOIN returns the matching records from table1 and table2:

Inner Joins

Syntax

The syntax for the INNER JOIN in SQL is:

SELECT columns
FROM table1

INNER JOIN table2
ON table1.column = table2.column;

Self Join

A table is joined to itself utilizing Self JOIN. It implies that each table line is joined with itself and with each and every other table column.

Oneself JOIN can be considered a JOIN of two duplicates of similar tables.

We can do this with the assistance of table name pseudonyms to relegate a particular name to each table's example.

The table monikers empower us to utilize the table's impermanent name that we will use in the question.

It's a useful way to extract hierarchical data and compare rows inside a single table. Best example: To get an Employee and Manager relationship.

Syntax

The syntax for the SELF JOIN in SQL is:

SELECT columns  

FROM table1 T1, table1 T2   
ON T1.column = T2.column;

CROSS JOIN

In SQL Server, the CROSS JOIN operation allows you to combine multiple tables and retrieve a result set that includes every possible combination of rows from those tables. This operation is also referred to as a "Cartesian join" because it generates the Cartesian product of the linked tables. The Cartesian product represents the combination of all rows from the first table multiplied by all rows from the second table.

The below visual representation illustrates the CROSS JOIN. It will give all the records from table1 and table2 where each row is the combination of rows of both tables:

Cross Join

Syntax

The syntax for the CROSS JOIN in SQL is:

SELECT columns

FROM table1
CROSS JOIN table2

OUTER JOIN

OUTER JOIN in SQL Server returns all records from both tables that satisfy the join condition. In other words, this join will not return only the matching record but also return all unmatched rows from one or both tables.

We can categorize the OUTER JOIN further into three types:

  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
  • LEFT OUTER JOIN

The LEFT OUTER JOIN retrieves all the records from the left table and matching rows from the right table. It will return NULL when no matching record is found in the right-side table. Since OUTER is an optional keyword, it is also known as LEFT JOIN.

The below visual representation illustrates the LEFT OUTER JOIN:

Left Outer join

Syntax

The syntax for the LEFT OUTER JOIN in SQL is:

SELECT columns
FROM table1

LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

RIGHT OUTER JOIN

The RIGHT OUTER JOIN retrieves all the records from the right-hand table and matched rows from the left-hand table. It will return NULL when no matching record is found in the left-hand table. Since OUTER is an optional keyword, it is also known as RIGHT JOIN.

The below visual representation illustrates the RIGHT OUTER JOIN:

Right Outer join

Syntax

The syntax for the RIGHT OUTER JOIN in SQL is:

SELECT columns
FROM table1

RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

FULL OUTER JOIN

The FULL OUTER JOIN in SQL Server returns a result that includes all rows from both tables. The columns of the right-hand table return NULL when no matching records are found in the left-hand table. And if no matching records are found in the right-hand table, the left-hand table column returns NULL.

The below visual representation illustrates the FULL OUTER JOIN:

 

Full Outer join

Syntax

The syntax for the FULL JOIN in SQL is:

SELECT columns
FROM table1

FULL [OUTER] JOIN table2
ON table1.column = table2.column;

SQL Script to understand the joins

For demo purposes, I have created a database SQLJoin.

/*

CREATE DATABASE SQLJoin

GO

USE SQLJoin

GO

*/

-- CREATE EMPLOYEE TABLE

CREATE TABLE dbo.EMP_MASTER
(
    EMP_ID INT NOT NULL,
    EMP_NAME VARCHAR(100) NOT NULL,
    GENDER CHAR(1),
    CITY_ID INT NULL,
    DESIGNATION VARCHAR(100),
    TEAM VARCHAR(50),
    MGR_ID INT
)

-- CREATE CITY TABLE

CREATE TABLE dbo.CITY_MASTER
(
    CITY_ID INT IDENTITY(1,1),
    CITY_NAME VARCHAR(100) NOT NULL
)

-- DUMMY DATA

-- INSERT SOME CITY DATA

INSERT INTO CITY_MASTER(CITY_NAME)
SELECT 'PUNE'
UNION ALL
SELECT 'NAGPUR'
UNION ALL
SELECT 'LUCKNOW'
UNION ALL
SELECT 'MUMBAI'
UNION ALL
SELECT 'NOIDA'
UNION ALL
SELECT 'GURGOAN'
UNION ALL
SELECT 'DELHI'
UNION ALL
SELECT 'BANGALORE'

GO

-- INSERT SOME EMPLOYEE DATA

INSERT INTO dbo.EMP_MASTER
(
    EMP_ID, EMP_NAME, GENDER, TEAM, MGR_ID, DESIGNATION, CITY_ID
)
SELECT 13601, 'SANJAY', 'M', 'DCMT', 14001, 'Level 3', 1
UNION ALL
SELECT 13602, 'DEEPIKA', 'F', 'DCMT', 14001, 'Level 2', 2
UNION ALL
SELECT 13603, 'PANKAJ', 'M', 'DCMT', 14001, 'Level 1', NULL
UNION ALL
SELECT 14740, 'Mayuri', 'F', 'DCMT', 1954, 'Level 1', NULL
UNION ALL
SELECT 14732, 'VIVEK', 'M', 'Exchange', 1954, 'Level 3', 4
UNION ALL
SELECT 14733, 'Prashant', 'M', 'Exchange', 1201, 'Level 3', 5
UNION ALL
SELECT 14734, 'Kishor', 'M', 'M365', 1201, 'Level 3', 6
UNION ALL
SELECT 14001, 'MANISH', 'M', 'Managed Services', 2704, 'Level 4', 3
UNION ALL
SELECT 1201, 'SHANKAR', 'M', 'Managed Services', 2704, 'Level 4', 6
UNION ALL
SELECT 1954, 'HARESH', 'M', 'Delivery Center', 2704, 'Level 4', 4
UNION ALL
SELECT 2704, 'NARAYAN', 'M', 'IT HEAD', NULL, 'SR. IT VP', NULL

Get data from both tables.

Table

/*
    WORKING WITH JOINS

    1. Inner Join
    2. Self Join
    3. Cross Join
    4. Left Outer Join
    5. Right Outer Join
    6. Full Outer Join
*/

/*
SQL SYNTAX FOR INNER JOIN
*/

SELECT
    EMP_ID,
    EMP_NAME,
    GENDER,
    DESIGNATION,
    TEAM,
    CITY.CITY_NAME
FROM
    dbo.EMP_MASTER EMP
INNER JOIN
    dbo.CITY_MASTER CITY
ON
    EMP.CITY_ID = CITY.CITY_ID;

Output

Output

/*
SQL SYNTAX FOR SELF JOIN
*/

SELECT
    EMP.EMP_ID,
    EMP.EMP_NAME,
    EMP.TEAM,
    EMP.DESIGNATION,
    MGR.EMP_ID AS MANAGER_ID,
    MGR.EMP_NAME AS MANAGER_NAME
FROM
    EMP_MASTER EMP
LEFT JOIN
    EMP_MASTER MGR
ON
    EMP.MGR_ID = MGR.EMP_ID
ORDER BY
    EMP.MGR_ID,
    EMP.EMP_ID;

Output

Output

SELECT
    EMP.EMP_ID,
    EMP.EMP_NAME,
    EMP.TEAM,
    EMP.DESIGNATION,
    MGR.EMP_ID AS MANAGER_ID,
    MGR.EMP_NAME AS MANAGER_NAME
FROM
    EMP_MASTER EMP
LEFT JOIN
    EMP_MASTER MGR ON EMP.MGR_ID = MGR.EMP_ID
WHERE
    MGR.EMP_ID = 14001
ORDER BY
    EMP.MGR_ID,
    EMP.EMP_ID;

Output

Output

/*
SQL SYNTAX FOR CROSS JOIN
*/

SELECT EMP_ID, EMP_NAME, GENDER,
       DESIGNATION, TEAM, CITY.CITY_NAME
FROM dbo.EMP_MASTER EMP
CROSS JOIN dbo.CITY_MASTER CITY
ORDER BY EMP_ID;
-- ON EMP.CITY_ID = CITY.CITY_ID -- WHERE CONDITION IS NOT REQUIRED.

Output

Output

/*
SQL SYNTAX FOR LEFT OUTER JOIN
*/

SELECT
    EMP_ID,
    EMP_NAME,
    GENDER,
    DESIGNATION,
    TEAM,
    CITY.CITY_NAME
FROM
    dbo.EMP_MASTER EMP
LEFT OUTER JOIN
    dbo.CITY_MASTER CITY
ON
    EMP.CITY_ID = CITY.CITY_ID;

Output

Output

/*
SQL SYNTAX FOR RIGHT OUTER JOIN
*/

SELECT EMP_ID, EMP_NAME, GENDER,
       DESIGNATION, TEAM, CITY.CITY_NAME
FROM dbo.EMP_MASTER EMP
RIGHT OUTER JOIN dbo.CITY_MASTER CITY
ON EMP.CITY_ID = CITY.CITY_ID;

Output

Output

/*
SQL SYNTAX FOR FULL OUTER JOIN
*/

SELECT EMP_ID, EMP_NAME, GENDER,
       DESIGNATION, TEAM, CITY.CITY_NAME
FROM dbo.EMP_MASTER EMP
FULL OUTER JOIN dbo.CITY_MASTER CITY
ON EMP.CITY_ID = CITY.CITY_ID;

Output

Output

We can add the where condition in the above queries to get the desired output as per requirement, except Cross Join.

Through Graphical Interface.

Data in tables

Table

Inner Join

Inner join

Left Outer Join

Left outer join

Right Outer Join

Right Outer join

Full Outer Join

Full outer join

Cross Join

Cross Join


Similar Articles