SQL Basics

• Normalization often distributes data across several tables
• A JOIN lets a query retrieve data from multiple tables
• To extend one table's data with data from another, you need a matching condition linking the tables
• Usually this matches a foreign key in one table to a primary key in the other
• Use the data model to determine the correct join

Inner Join

• A JOIN extends the main table's result with columns from the joined table
• There are several join types, including inner and outer joins. Outer joins are covered in the next chapter
• An inner join can be written in two ways

-- Also called an equijoin: testing equality
SELECT <column1>, <column2>
FROM   <table1>, <table2>
WHERE  <table1.key1> = <table2.key2>	

SELECT <column1>, <column2>
FROM   <table1>
       INNER JOIN <table2> ON <table1.key1> = <table2.key2>	

• The usual standard is method 2, which explicitly uses INNER JOIN

Select all product names, product prices and their vendor names

-- Oracle / MySQL / SQL Server
SELECT Vendors.vend_name,
       Products.prod_name,
       Products.prod_price
FROM   Vendors
       INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

-- Oracle / MySQL / SQL Server	
SELECT Vendors.vend_name,
       Products.prod_name,
       Products.prod_price
FROM   Products
       INNER JOIN Vendors ON Products.vend_id = Vendors.vend_id;

• Without a condition matching the primary and foreign keys, the result is a Cartesian product
• A Cartesian Product combines every row in the first table with every row in the second table
• This query produces a Cartesian product containing 54 rows

SELECT Vendors.vend_name,
       Products.prod_name,
       Products.prod_price
FROM   Products, Vendors;

• 6 rows from Vendors × 9 rows from Products
• A Cartesian product is also called a cross join
• A Cartesian product is not the intended result in this example

• SQL does not specify a fixed limit on the number of tables that can be joined
• The DBMS may impose its own limit

-- Oracle / MySQL / SQL Server
SELECT Customers.cust_name AS KlantNaam,
       Customers.cust_contact AS KlantContact,
       Products.prod_name AS ProductNaam,
       Vendors.vend_name AS Leverancier
FROM   Customers
       INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
       INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
       INNER JOIN Products ON OrderItems.prod_id = Products.prod_id
       INNER JOIN Vendors ON Products.vend_id = Vendors.vend_id
WHERE  OrderItems.prod_id = 'RGAN01';

51. Select the vendor countries and product names (9)

52. Select all order dates and the total quantity ordered for each order (5)

53. Select the names of customers who ordered at least 100 units of an item on an individual order line (2)

54. Select customer names and the names of the products they ordered (18)

55. Select customer names and the total price of their ordered products, where the total exceeds 2000 (1)