CST 363 Intro to Database Systems Module 2: Advanced SQL

2.1 SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ).  As long the column(s) are type compatible 98% of the time, the join uses the primary key of one table, the foreign key of the other table and equal predicate.  Think of example where joining on something other than keys would be needed.  Write the query both as English sentence and SQL.  If you can't think of your own example, search the internet for an example.

 There are different types of JOINS that you can do in SQL. They include: 

INNER JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

 

Here are examples on how to write an SQL query with those joins.

INNER JOIN: The following SQL statement selects all orders with customer information:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN: The following SQL statement will select all customers, and any orders they might have:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

RIGHT JOIN: The following SQL statement will return all employees, and any orders they might have placed:

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;  

FULL JOIN: The following SQL statement selects all customers, and all orders:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;