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;
SELECT Item.ItemID, Item.ItemDesc, Artist.ArtistName
FROM Item
JOIN Artist ON Item.ArtistID = Artist.ArtistID;
WHERE item.ItemCost BETWEEN 100 AND 300;
Use 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%';
- Avoid using WHERE with aggregate functions like COUNT.
- Instead, use HAVING after GROUP BY.
Ordering and Grouping Data
- ORDER BY: Sorts results in ascending ( ASC) or descending ( DESC) order.
- GROUP BY: Groups rows sharing a property, often used with aggregate functions.
- HAVING: Filters groups based on aggregate conditions.
SELECT ArtistName, COUNT(ItemID) AS ItemCount
FROM Item
JOIN Artist ON Item.ArtistID = Artist.ArtistID
GROUP BY ArtistName
HAVING COUNT(ItemID) = 2
ORDER BY ArtistName ASC;
ORDER BY is applied after GROUP BY to sort the grouped results.
Practical Example: A Comprehensive Query
- Consider a database with two tables: Customer and Purchase.
- You want to find all customers from Italy who purchased items between July 28, 2025, and July 31, 2025.
SELECT Customer.CustomerName, Purchase.ItemID, Purchase.PurchaseDate
FROM Purchase
JOIN Customer ON Purchase.CustomerID = Customer.CustomerID
WHERE (Customer.Country = 'Italy' OR Customer.Country = 'France') AND Purchase.PurchaseDate BETWEEN '2025-07-28' AND '2025-07-31';
This query uses a JOIN to combine data from two tables, filters results with WHERE, and applies multiple conditions using AND and OR.
Key Takeaways
- Joins: Essential for combining data from multiple tables.
- Filtering: Use WHERE, BETWEEN, and LIKE to refine results.
- Ordering and Grouping: ORDER BY, GROUP BY, and HAVING organize and summarize data.
- Logical Operators: AND, OR, and NOT build complex conditions.
