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

Table Products

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:

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:

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:

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:

More Related Articles For You

• Architecture of DBMS
• CODDS RULE DBMS
• Database Models in DBMS
• Relational DBMS Concepts
• Keys and Types of keys in Database
• Database Normalization
• Generalization, Specialization and Aggregation Concepts in DBMS
• ERD Diagram Tutorial with Examples in DBMS
• Introduction to SQL
• How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
• Alter Query Command in SQL, Add, Modify and Drop Column in Table
• TCL Commands in SQL
• Truncate Query, Drop and Rename Query in SQL
• All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
• Data Control Language DCL Revoke and Grant Command in SQL
• Select Statement or Select Query in SQL Explained with Examples
• Distinct Keyword Explained in SQL
• WHERE Statement or WHERE Clause in SQL Statement Explained
• AND & OR Operators in SQL
• LIKE Operator in SQL
• ORDER BY Clause Sorting Explained in SQL
• Group By Clause in SQL
• Having Clause Explained in SQL
• SQL Constraints
• SQL Aggregate Functions
• SQL Aliases Use and Purpose in SQL
• SQL Sequence Syntax and Use with Examples
• SQL View
• SET Operation in SQL, UNION, UNION ALL, Intersect and Minus

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes