5.SQL Joins.sql

use ecom;

-- What are joins?
-- Simply put, joins are used in SQL when we want to join multiple tables into one.

create table price(
		order_id int,
        cust_id int,
        price float
	);

insert into price values (1,101,1000),(2,201,1100),(3,501,1200);
    
create table customer(
		id int,
        name varchar(40),
        email varchar(50)
	);

insert into customer values (101,'athira','aa'),(201,'aswathi','bb'),(301,'anuvind','cc');

select * from price;
select * from customer;

-- Inorder to apply join, we need a joining key. Here, cust_id from price table and id from customer table.

-- Types of join
-- INNER JOIN  : Intersection. ie, record should be available in both the tables.

select id, order_id, price, name, email  -- for same column names use tablename.columnname
from price
inner join customer
on price.cust_id = customer.id;

select p.*,c.name 
from price p  -- We can use alias for table names as well   to make query simple.
inner join customer c
on p.cust_id = c.id;

select *
from price p 
join customer c -- By default, join is inner join
on p.cust_id = c.id;

-- LEFT JOIN  : All the records from left table no matter weather record is available in both the tables or not.

select *
from price
left join customer
on cust_id = id;  -- The extra record in left table will be filled with null values for columns in right table.

-- RIGHT JOIN  : All the records from right table no matter weather record is available in both the tables or not.

select *
from price
right join customer
on cust_id = id;  -- The extra record in right table will be filled with null values for columns in left table.

-- FULL JOIN : All the records from both table will be present. MySQL does not support FULL OUTER JOIN, we use a UNION on LEFT and RIGHT join

select *
from price p  
left join customer c
on p.cust_id = c.id
union  
select *
from price
right join customer
on cust_id = id; 

drop table price;
drop table customer;