SQL Joins Types, Use and Purpose

Different Types of SQL Joins

SQL Joins are utilized to link rows from two or more than two tables. Join clause is used to merge the rows in multiple tables on the basis of a common field that exists in those tables. This linking can either be parallel or in series form, means, that there can be one to many and one to one linking of tables.

Different Types of Joins

Following are the different types of SQL JOINs that are commonly used:

  • INNER JOIN: Returns all rows when there is at least one value matches in BOTH tables
  • LEFT JOIN: Return all rows from the left table, and only the matched rows from the right table
  • RIGHT JOIN: Return all rows from the right table, and only the matched rows from the left table
  • FULL JOIN: Return all rows when there is a match in any of the tables

Demo Tables

Table Category

Cat_ID Category_Name
1 Keyboard
2 Mouse
3 LCD Display
4 Camera
5 Printer
6 Scanner
7 Laptop

Table Products

ID Cat_ID Product_Name Price
1 1 A4_Tech Keyboard 32
2 2 A4_Tech Optical Mouse 28
3 3 HP 17” LCD Display Unit 140
4 1 A4_Tech Wireless Keyboard 80
5 3 Philips 17” LED Display Unit 125
6 5 HP Laser Jet 1020 Printer 175
7 1 Bluetooth Keyboard 125
8 4 A4_Tech HD WebCam 60


Syntax

INNER JOIN

SELECT col_name_1, col_name_2, …, col_name_n
FROM tbl_name_1
INNER JOIN tbl_name_2
ON tbl_name_1.col_name_1=tbl_name_2.col_name_1;

Or

SELECT col_name_1, col_name_2, …, col_name_n
FROM tbl_name_1
JOIN tbl_name_2
ON tbl_name_1.col_name_1=tbl_name_2.col_name_1;

LEFT JOIN

SELECT col_name_1, col_name_2, …, col_name_n
FROM tbl_name_1
LEFT JOIN tbl_name_2
ON tbl_name_1.col_name_1=tbl_name_2.col_name_1;

Or

SELECT col_name_1, col_name_2, …, col_name_n
FROM tbl_name_1
LEFT OUTER JOIN tbl_name_2
ON tbl_name_1.col_name_1=tbl_name_2.col_name_1;

RIGHT JOIN

SELECT col_name_1, col_name_2, …, col_name_n
FROM tbl_name_1
RIGHT JOIN tbl_name_2
ON tbl_name_1.col_name_1=tbl_name_2.col_name_1;

Or

SELECT col_name_1, col_name_2, … , col_name_n
FROM tbl_name_1
RIGHT OUTER JOIN tbl_name_2
ON tbl_name_1.col_name_1=tbl_name_2.col_name_1;

FULL JOIN

SELECT col_name_1, col_name_2, … , col_name_n
FROM tbl_name_1
FULL OUTER JOIN tbl_name_2
ON tbl_name_1.col_name_1=tbl_name_2.col_name_1;

Example

INNER JOIN

SELECT Products.ID, Category.Category_Name, Products.Name, Products.Price
FROM Category
INNER JOIN Products
ON Category.Cat_ID=Products.Cat_ID
ORDER BY CAT_ID;

The above command will return following:

ID Category_Name Product_Name Price
1 Keyboard A4_Tech Keyboard 32
2 Mouse A4_Tech Optical Mouse 28
3 LCD Display HP 17” LCD Display Unit 140
4 Keyboard A4_Tech Wireless Keyboard 80
5 LCD Display Philips 17” LED Display Unit 125
6 Printer HP Laser Jet 1020 Printer 175
7 Keyboard Bluetooth Keyboard 125
8 Camera A4_Tech HD WebCam 60

LEFT JOIN

SELECT Products.ID, Category.Category_Name, Products.Name, Products.Price
FROM Category
LEFT OUTER JOIN Products
ON Category.Cat_ID=Products.Cat_ID
ORDER BY CAT_ID;

The above query return following:

ID Category_Name Product_Name Price
1 Keyboard A4_Tech Keyboard 32
4 Keyboard A4_Tech Wireless Keyboard 80
7 Keyboard Bluetooth Keyboard 125
2 Mouse A4_Tech Optical Mouse 28
3 LCD Display HP 17” LCD Display Unit 140
5 LCD Display Philips 17” LED Display Unit 125
8 Camera A4_Tech HD WebCam 60
6 Printer HP Laser Jet 1020 Printer 175
null Scanner Null null
null Laptop Null null

RIGHT JOIN

SELECT Products.ID, Category.Category_Name, Products.Name, Products.Price
FROM Category
RIGHT OUTER JOIN Products
ON Category.Cat_ID=Products.Cat_ID
ORDER BY CAT_ID;

The above query return following:

ID Category_Name Product_Name Price
1 Keyboard A4_Tech Keyboard 32
4 Keyboard A4_Tech Wireless Keyboard 80
7 Keyboard Bluetooth Keyboard 125
2 Mouse A4_Tech Optical Mouse 28
3 LCD Display HP 17” LCD Display Unit 140
5 LCD Display Philips 17” LED Display Unit 125
8 Camera A4_Tech HD WebCam 60
6 Printer HP Laser Jet 1020 Printer 175

FULL JOIN

SELECT Products.ID, Category.Category_Name, Products.Name, Products.Price
FROM Category
FULL OUTER JOIN Products
ON Category.Cat_ID=Products.Cat_ID
ORDER BY CAT_ID;

The above query return following:

ID Category_Name Product_Name Price
1 Keyboard A4_Tech Keyboard 32
4 Keyboard A4_Tech Wireless Keyboard 80
7 Keyboard Bluetooth Keyboard 125
2 Mouse A4_Tech Optical Mouse 28
3 LCD Display HP 17” LCD Display Unit 140
5 LCD Display Philips 17” LED Display Unit 125
8 Camera A4_Tech HD WebCam 60
6 Printer HP Laser Jet 1020 Printer 175
null Scanner Null null
null Laptop Null null

More Related Articles For You

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes

Search

Shortcodes Ultimate

 
Follow Us
 
BGP