Join operations in SQL

Date Published: 25/08/2020Published By: JaiSchool

When we want to make relations between two or more tables, we take in use join operations.

We are going to understand these operations with example, so first, we will create a database, table, and then insert some data in it.

Database Name - jai_school

Query-

CREATE DATABASE jai_school;

Create two tables - admission and fee

admission table query- 

CREATE TABLE admission(
adm_no INT(9) NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
PRIMARY KEY(adm_no)
);

fee table query-

CREATE TABLE fee(
adm_no INT(9) NOT NULL AUTO_INCREMENT,
fee VARCHAR(50),
PRIMARY KEY(adm_no)
);

Insert some data in admission and fee table

Insert in admission table

INSERT INTO admission(name)VALUES("RAM"),("SHAYAM"),("RAMAN"),("AMAN"),("RAVI"),("ANKIT");

Insert in fee table

INSERT INTO fee(fee)VALUES(8000),(18000),(6000),(4500),(9000),(13000);

admission and fee table Image

Admission table image

admission_table-sql-joins

Fee table image

fee_table-sql-joins

Now, delete 2 and 5 number record from admission table & 3, 5 number record from fee table.

Tables image after deleting some of the records

Admission table

admission table after deleting records

Fee table

fee table after deleting some records

These are the join operators for stitching tables together-

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. FULL JOIN
  5. SELF JOIN

The clause ON will be used with joins to define condition instead of WHERE clause.

INNER JOIN operation

Example-

SELECT admission.name,fee.fee FROM admission INNER JOIN fee ON admission.adm_no = fee.adm_no;

Query result:-

INNER JOIN example

LEFT JOIN operation

Example-

SELECT admission.name,fee.fee FROM admission LEFT JOIN fee ON admission.adm_no = fee.adm_no;

Query result:-

LEFT JOIN example

RIGHT JOIN operation

Example-

SELECT admission.name,fee.fee FROM admission RIGHT JOIN fee ON admission.adm_no = fee.adm_no;

Query result:-

RIGHT JOIN example

FULL JOIN operation

Example-

SELECT admission.name, fee.fee FROM admission FULL OUTER JOIN fee
ON admission.adm_no = fee.adm_no;

MySql RDBMS does not support FULL JOIN. Use Oracle or Microsoft SQL server to test this query.

SELF JOIN operation

The SELF JOIN does not apply to another table. It is the relation on the same table. We are going to create an another table that will have its relationship on its own.

members Table-

mem_idmem_namerel_id
1Ram2
2Ravi4
3Ankit6
4Anil7
5Rakesh1
6Aman5
7Shyam3

Query for members table-

CREATE TABLE members(mem_id INT NOT NULL AUTO_INCREMENT,mem_name VARCHAR(30),rel_id INT(10),PRIMARY KEY(mem_id));
INSERT INTO members(mem_name,rel_id)VALUES("Ram",2),("Ravi",4),("Ankit",6),("Anil",7),("Rakesh",1),("Aman",5),("Shyam",3);

In this table, Ram's rel_id (relation_id) is 2 so he has any kind of relationship with the member whose mem_id (member id) is 2 (Ravi).

Similarly, Anil's rel_id (relation_id) is 7 so he has any kind of relation with with the member whose mem_id (member id) is 7 (Shyam) and so on.

Example-

SELECT a.mem_name AS member_name, b.mem_name AS related_id
FROM members AS a
INNER JOIN members AS b
ON a.mem_id = b.rel_id;

Query result-

SELF JOIN example