Constructing Queries Between Two Tables in SQL
Understanding Joins in SQL
- Inner Join: Returns rows with matching values in both tables.
- Left Join: Returns all rows from the left table and matching rows from the right table. If no match, NULLs are returned.
- Right Join: Returns all rows from the right table and matching rows from the left table. If no match, NULLs are returned.
The JOIN clause is essential for combining data from multiple tables, allowing you to create comprehensive queries that leverage relationships between entities.
Basic Syntax of a Join
SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.common_column = table2.common_column;
ExampleSELECT Item.ItemID, Item.ItemDesc, Artist.ArtistName
FROM Item
JOIN Artist ON Item.ArtistID = Artist.ArtistID;
WHERE item.ItemCost BETWEEN 100 AND 300;
TipUse BETWEEN for inclusive range queries, and LIKE with % for flexible pattern matching.
Combining Conditions with AND, OR, NOT
- AND: All conditions must be true.
- OR: At least one condition must be true.
- NOT: Excludes specific conditions.
SELECT ItemDesc, ArtistName FROM Item JOIN Artist ON Item.ArtistID = Artist.ArtistID WHERE NOT ArtistName LIKE '%Ceramics%';
