Full Join
"Gimme everything from both tables"
The logic of FULL JOIN is to get everything from both tables regardless if they match or not !
Lets look at the most basic query:
SELECT * FROM customers FULL JOIN orders ON customers.id = orders.customer_id;
Now this query will output a merged table containing everything from both the tables as simple as that !
Now one question that I had that its doing almost the same thing as LEFT JOIN shows everything left and also has matching values (everything) from the right.
The issue is that in our case, the orders table contains records that have a valid customer so regardless of a LEFT JOIN or FULL JOIN it will return everything from the orders table. That means while doing a LEFT JOIN all the rows in the orders table have a valid match with the customers table.
To better see this we enter an invalid entry in the orders table:
INSERT INTO orders
VALUES('OD6', 78, 'Not a valid customer id', 100, 'NOT ACCEPTED');
This enters a order with an invalid customer_id which does not exists in the customers table. Now performing a LEFT JOIN on this will exclude this record as its not matching with any row from the left table (customers).
This is where FULL JOIN comes in !
Doing a FULL JOIN here will show this record as its a valid entry in the orders table.
That's the whole gist of FULL JOIN !
Follow Coming Soon to continue from here.
Browse Full SQL Page.