Right Join
“Give me everything from the RIGHT table, and only matching rows from the LEFT table.”
Its literally the opposite of LEFT JOIN...we get everything from RIGHT table and only matching values from the LEFT table.
Lets look at questions directly !
Q. Show all orders and the names of the customers who placed them. But include every order, even if its customer doesn’t exist in the customers table.
S.
SELECT customers.name, orders.order_id, orders.item, orders.price FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;
The logic is simple we are using RIGHT JOIN rather than LEFT JOIN so all the rows from the right table (orders) appear. This is exactly the opposite of what we saw with LEFT JOIN where all the rows from the left table (customers) were appearing !
There's nothing much to discuss.
Q. Show each order, and also show whether the customer exists or got deleted.
If the customer exists -> show their name,
If not -> show "DELETED CUSTOMER" instead.
S. This might look a bit tricky but is easy with the COALESCE() function, lets see:
SELECT orders.order_id, orders.item, orders.price, COALESCE(customers.name, 'DELETED CUSTOMER') FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;
The whole logic is the same, we RIGHT JOIN on orders and get each row from that table.
The COALESCE() function returns the name of customer for each row if they exists and if they don't exist it shows "DELETED CUSTOMER" !
That was RIGHT JOINs.
Follow The JOIN Picture to continue from here.
Browse Full SQL Page.