preview

Subqueries And Conqueries Essay

Decent Essays
Open Document

Arizona State University
W. P. Carey School of Business
CIS 360
The NorthWind Database Tutorial

PART 3 – Subqueries & Joins
In this lesson of the NorthWind SQL tutorial, you will learn...
1. To write queries with subqueries.
2. To select columns from multiple tables with joins.
3. To select records from multiple tables with unions.

Subqueries
Subqueries are queries embedded in queries. They are used to retrieve data from one table based on data in another table. They generally are used when tables have some kind of relationship. For example, in the NorthWind database, the Orders table has a
CustomerID field, which references a customer in the Customers table. Retrieving the
CustomerID for a specific order is pretty straightforward.

Code …show more content…

Table Aliases
Using full table names as prefixes can make SQL queries unnecessarily wordy. Table aliases can make the code a little more concise. The example below, which is identical in functionality to the query above, illustrates the use of table aliases.

6

Code Sample 6
-- Create a report showing employee orders using Aliases.
SELECT e.EmployeeID, e.FirstName, e.LastName,
o.OrderID, o.OrderDate
FROM Employees e JOIN Orders o ON
(e.EmployeeID = o.EmployeeID)
ORDER BY o.OrderDate;

Multi-table Joins
Multi-table joins can get very complex and may also take a long time to process, but the syntax is relatively straightforward.
Syntax
SELECT table1.column, table2.column, table3.column
FROM table1
JOIN table2 ON (table1.column=table2.column)
JOIN table3 ON (table2.column=table3.column)
WHERE conditions

Note that, to join with a table, that table must be in the FROM clause or must already be joined with the table in the FROM clause. Consider the following.
SELECT table1.column, table2.column, table3.column
FROM table1
JOIN table3 ON (table2.column=table3.column)
JOIN table2 ON (table1.column=table2.column)
WHERE conditions

The above code would break because it attempts to join table3 with table2 before table2 has been joined with table1.

Code Sample 7
/*
Create a report showing the Order ID, the name of the company that
placed

Get Access
Related
Get Access