How to use join with union in MySQL
To combine rows from two or more tables, based on a related column between them, we can use JOIN clause. To show more than one select query result in one table, we can use UNION clause. Imagine that there are 3 tables called customers, food_orders and drink_orders. The customers have a field called id, and customer_name. and for the food_orders and drink_orders there are field called customer_id and order_date. customers tables | id | customers_name | food_orders tables | customers_id | order_date | drink_orders tables | customers_id | order_date | When i want to know the name of customer who order the food or drink i need to JOIN the table with the customer tables. The code below is the example of join query to get the name of customers who order the food.
SELECT customers.id, customers.customer_name, food_orders.order_date FROM Orders INNER JOIN Customers ON customers.id=food_orders.customer_id;
To get the output of joining customers with food_orders table and customers with drink_orders in the same table. We can use the UNION clause. To use the UNION clause, the selected column name must have a same name.
SELECT customers.id, customers.customer_name, food_orders.order_date FROM Orders INNER JOIN Customers ON customers.id=food_orders.customer_id UNION SELECT customers.id, customers.customer_name, drink_orders.order_date FROM Orders INNER JOIN Customers ON customers.id=drink_orders.customer_id
Union combines the results from two or more queries into a single result set that includes all rows that belong to all queries in the union. Using join , you can retrieve data from two or more tables based on the logical relationship between the tables. Joins show how SQL should use data from one table to select rows in another table. There are several types of join. This types are inner join, left join, right join, and full join. The union operation is different with join which union combines columns from two or more tables. You can use union and join in the same query.
