Joins in SQL : Comprehensive Examples and Code Snippets

At its core, an SQL join allows you to merge rows from two or more tables based on a related column between them. This relational prowess enables us to transcend the boundaries of individual tables and extract holistic information that is greater than the sum of its parts.

For execution i’ve created a sample tables Students, Information and inserted a value to the tables. These tables will be used as base to show all the joins in SQL.

-- creating a table students
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);
-- insert values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
INSERT INTO students VALUES (3, 'chandan', 'M');
INSERT INTO students VALUES (4, 'ravi', 'M');
INSERT INTO students VALUES (5, 'flash', 'M');
INSERT INTO students VALUES (6, 'prateek', 'M');

-- creating a table information
create table information (id integer primary key ,
phone integer
);
-- insert values
insert into information values (1, 323232324);
insert into information values (2, 324234544);
insert into information values (3, 453543543);
insert into information values (4, 345342332);
insert into information values (5, 234234234);
insert into information values (6, 234234543);
insert into information values (7, 345435435);
insert into information values (8, 434534543);


-- fetch values
SELECT * FROM information

Students Table

Information Table

Left Join

A left join retrieves all records from the left table and matching records from the right table. This is particularly useful when you want to include all elements from the primary table, even if there's no direct match in the secondary table.

Consider the above tables Students and Information to match phone numbers of all students.

SELECT * FROM students a
left join information b on b.id = a.id

Right Join

A right join is essentially the inverse of a left join. It includes all records from the right table and matching records from the left table. This can be handy in scenarios where you want to prioritise the data on the right side.

SELECT * FROM students a
right join information b on b.id = a.id

output

Inner join

The inner join is the foundation of data synthesis. By matching rows with common values in the specified columns, this join type retrieves only the intersecting records, discarding non-matching ones.

SELECT * FROM students a
inner join information b on b.id = a.id

output

Full outer join

When you need a comprehensive view of data, the full outer join comes into play. It retrieves all records when there's a match in either the left or the right table, showcasing a holistic picture.

SELECT * FROM students a
full outer join information b on b.id = a.id

output

Self join

Self join helps to analyse one table with itself. This join is used to mainly look into trends in the past data. Various conditions can be given ie a date condition , id, gender etc

SELECT * FROM students a
join students b on b.id = a.id 
where a.gender = 'M'

output

Cross join

A cross join returns the Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table. Cross joins can result in large result sets because each row from table A will be matched with a row from table B.

SELECT * FROM students a
cross join students b on b.id = a.id