JOIN in SQL

Join is creating new table ( virtual ) by combining tables

• Inner join
• Left join
• Right join

Tables are

Creating 2 tables

Table A — Person

Table B — Car

CREATE TABLE car (
 id BIGSERIAL NOT NULL PRIMARY KEY,
 model VARCHAR(100) NOT NULL,
 price NUMERIC(19,2) NOT NULL
)

Add some data in this table

insert into car (model,price) values ('AUDI',200000);
insert into car (model,price) values ('MERCEDES',500000);

And for person table

CREATE TABLE person (
 id BIGSERIAL NOT NULL PRIMARY KEY,
 first_name VARCHAR(50) NOT NULL,
 last_name VARCHAR(50) NOT NULL,
 email VARCHAR(150),
 car_id BIGINT REFERENCES car(id)
)

Add data in person table

insert into person (first_name,last_name,email,car_id) values ('TONY','STARK','tonystark@stark.com',1);
insert into person (first_name,last_name,car_id) values ('ELON','MUSK',2);
insert into person (first_name,last_name) values ('RAJAN','LAGAH');

We are using car_id as foreign key here. Foreign key each value represent unique entry in other table.

Inner Join

SELECT * FROM person
JOIN car ON person.car_id = car.id;

we get data where we have person’s details of those having car

Left join

SELECT * FROM person
LEFT JOIN car ON person.car_id = car.id;

we get data where we all the data from left (person) table is available with or without data from right ( car ) table

Right join

SELECT * FROM person
RIGHT JOIN car ON person.car_id = car.id;

All the data of right ( car ) table with or without left ( person ) table

Done.

Do watch this video for more info.