Notes for a refresher and or quick reference for SQL.
JOIN chart for visualizing the different types of possible JOIN. Source: https://www.w3schools.com/sql/sql_join.asp
Source from below: https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
For a INNER JOIN , it can be re-written as such below and is called an IMPLICIT INNER JOIN
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT * FROM TableA A, TableB B WHERE A.Key = B.Key above is the same as SELECT * FROM TableA A INNER JOIN TableB B ON A.Key=B.Key Using INNER JOIN is convention. INNER is also not needed as all joins are inner joins unless specified. |
There is no difference between RIGHT JOIN and RIGHT OUTER JOIN. Both are the same. LEFT JOIN and LEFT OUTER JOIN are also the same.
IFNULL function. If it is going to return a NULL value you can set a replacement value instead. Useful for LEFT/RIGHT JOINS where a column maybe be NULL
So for example:
|
1 2 3 4 5 6 7 8 9 |
SELECT first_name, last_name, IFNULL(SUM(amount),0) AS total FROM customers LEFT JOIN orders ON customers.id=orders.customer_id // If there is no orders found for a particlur customer.id, it will // return 0 for total instead of NULL |