365 Days of Daily Coding: Day 117

The entire advanced sql site has been completely revamped which is nice but also quite a bummer as the previous questions have been replaced with new questions. So that means I will have to work my way through solution 1 again, just as I was determined to complete all of them. No worries! Let’s start again.

Puzzle 1:

I used the live oracle environment here to test the solution.

My solution:

WITH t1 AS (
Select 'Sugar' AS Item FROM Dual Union All
Select 'Bread' AS Item FROM Dual Union All
Select 'Juice' AS Item FROM Dual Union All
Select 'Soda' AS Item FROM Dual Union All
Select 'Flour' AS Item FROM Dual
), t2 AS (
Select 'Sugar' AS Item FROM Dual Union All
Select 'Bread' AS Item FROM Dual Union All
Select 'Butter' AS Item FROM Dual Union All
Select 'Cheese' AS Item FROM Dual Union All
Select 'Fruit' AS Item FROM Dual)

SELECT	t1.Item AS ItemCart1,
		t2.Item AS ItemCart2
FROM	t1 FULL OUTER JOIN
		t2 ON t1.Item = t2.Item;

Full outer join is a join that returns all records when there is match in the left table or right table. More information can be found here.