Sql SELECT from multiple tables

Deciphering JOIN types: read their minds

Different JOIN types shape data differently:

• INNER JOIN: The snob. Parties only with records that are common in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): The clingy type. Holds onto all records from the left table, and drags in matching records from the right table. If the right side has no match, "NULL" is their plus one.
• RIGHT JOIN (or RIGHT OUTER JOIN): Same as LEFT JOIN, but flips the roles. ALL records from the right table (including those with no match on the left side) march into the result set.
• FULL JOIN (or FULL OUTER JOIN): The extrovert. Invites all records when there's a match in either left or right table.

Bringing together customer and product data: the matchmaker

Need to display customer and product figures as a pair? A LEFT JOIN will do, ensuring all products are escorted by a customer, even if it's "NULL":

SELECT p.product_id, p.name AS product_name, c.customer_id, IFNULL(c.name, 'No Customer') AS customer_name
FROM products p
LEFT JOIN customers c ON p.customer_id = c.customer_id;


// @Santa, this fills up your stocking with NULL for naughty customers who didn't buy a product!

The UNION: the peace-maker

Sometimes, you want to unite similar data from different tables. UNION creates harmony by fusing two or more datasets:

SELECT column_name(s) FROM first_table
UNION
SELECT column_name(s) FROM second_table;


// Sure beats writing a constitution! Just remember to select the same number of columns, in the same order, with compatible data types. Or else… chaos!

Query optimization: your coding fitness regime

For big-league joins and unions:

• Adopt aliases to simplify your query life.
• Verify your join conditions. Don't match apples and oranges!
• Put UNION ALL in your toolkit for faster operation, when you're cool with duplicates.
• Index foreign keys. Your joins will thank you.