Left Join & Inner Join

JOIN function is used to combine two or more tables to create a single result set. JOIN typically defines the relationship between two tables based on specific conditions and combines rows accordingly. Major JOIN types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

 

However, I am going to write only about LEFT JOIN, INNER LEFT JOIN in this posting

 

1. LEFT JOIN includes all rows from the left table and only those rows from the right table where there is a match. If there's no match, it returns NULL.

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

 

 

2. INNER JOIN returns the intersection of two tables, meaning it generates results that only include rows that exist in both tables. INNER JOIN performs the connection for rows that exist in both tables.

SELECT *
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

 

 

Note, 

 

It was possible to assign the table name at the "FROM" line. It is much more convenient to use the assigned name instead of using the full name.

 

SELECT f.order_id,
f.customer_id,
f.restaurant_name,
f.price,
c.name,
c.age,
c.gender
FROM food_orders f left join customers c on f.customer_id=c.customer_id

 

 

Practice

 

Segmentation based on average food order amount per restaurant and the average age of customers.

 

Criteria for average food order amount: Below 5,000 / 5,000 to 10,000 / 10,000 to 30,000 / Above 30,000.

 

Criteria for average age: Up to 20s / 30s / 40s / 50s and above.

 

Retrieve data only when there is data in both tables, sorted in ascending order by restaurant name.

 

SELECT a.restaurant_name,
	   case when avg_price between 0 and 5000 then 'Price_group1'
	   		when avg_price between 5000 and 10000 then 'Price_group2'
	   		when avg_price between 10000 and 30000 then 'Price_group3'
	   		when avg_price>30000 then 'Price_group4' end as Price_group,
	   	case when avg_age between 0 and 30 then 'age_group1'
	   	     when avg_age between 30 and 40 then 'age_group2'
	   	     when avg_age between 40 and 50 then 'age_group3'
	   	     when avg_age>=50 then 'age_group4' end age_group
FROM 
(
SELECT f.restaurant_name,
	   avg(f.price) avg_price, 
	   avg(c.age) avg_age
FROM food_orders f inner join customers c on f.customer_id=c.customer_id
group by 1
) a
order by a.restaurant_name

 

Result

 

 

---