Join operations in SQL

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

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

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:-

LEFT JOIN operation

Example-

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

Query result:-

RIGHT JOIN operation

Example-

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

Query result:-

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_id	mem_name	rel_id
1	Ram	2
2	Ravi	4
3	Ankit	6
4	Anil	7
5	Rakesh	1
6	Aman	5
7	Shyam	3

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-

---

• || Display limited records from database | SQL LIMIT
• || Filter duplicate data | SQL DISTINCT
• || Get length of records in a table | SQL COUNT() function
• || Display data from a table based on the length of characters in a field | SQL length()
• || Handling NULL values in SQL
• || WHERE query on Booleans in SQL
• || Rename column in SQL
• || SQL constraints
• || HAVING clause in SQL
• || LIKE operator in SQL