Mastering SQL 2008 by sugee anbu

Information Resources

SQL (Structured Query Language)

?? The Select Statement ?? Specifying Criteria ?? Save a Query as a Table ?? Saving a Query ?? Modify Table Data ?? Deleting Records ?? Joining Two Tables ?? Joining More than 2 Tables (Inner Joins) ?? Outer Joins ?? Unions

Marshall School of Business University of Southern California

Table of Contents INTRODUCTION..............................................................................................................4 DISPLAYING INFORMATION FROM A TABLE...........................................................4 Proper Table Construction ...............................................................................................5 THE “SELECT” STATEMENT...........................................................................................6 Example 1: Select Columns, Specify a Criteria, & Sort Ascending....................................6 Example 2: Sorting In Descending Order ..........................................................................6 Example 3 – Selecting All Columns...................................................................................6 Example 4 – Hiding Duplicate Records (SELECT DISTINCT).........................................7 Example 5 – Hiding Duplicate Records (SELECT DISTINCT).........................................7 SPECIFYING CRITERIA ..................................................................................................7 Example 1: Using a Range Criteria....................................................................................7 Example 2: And Conditions Between Different Fields........................................................9 Example 3: Range BETWEEN two Points (Inclusive)........................................................9 Example 4: Range Between two Points (Exclusive)............................................................9 Example 5: An OR Condition...........................................................................................9 Example 6: Using the Not Operator..................................................................................9 Example 7: Using the <> Operator .................................................................................10 Example 8: Excluding Two Criteria .................................................................................10 Example 9: Searching for Blanks.....................................................................................10 Example 10: Excluding Blanks........................................................................................10 Example 11: LIKE (Using Wild Cards)...........................................................................10 Example 12: Using the IN Operator with a Number Field................................................11 Example 13: Using the IN Operator with a Text Field .....................................................11 CREATE A NEW TABLE FROM A QUERY..................................................................12 Example 1: Create a New Table from a Query – Standard SQL .....................................12 Example 2: Create a New Table from a Query– Access SQL.........................................12 SAVING A QUERY.........................................................................................................12 Example 1: Saving a Query (or VIEW in SQL) – Standard SQL....................................13 Deleting a table – “drop table”........................................................................................13 Deleting a view (query) – “drop veiw”............................................................................13 MODIFY DATA IN A TABLE.........................................................................................13 Example 1: Adding a New Record – Every Column........................................................13 Example 2: Adding a New Record – Selected Columns ..................................................13 EDITING EXISTING RECORDS ................................................................................14 Example 1: Updating a Specific Record with a Value ......................................................14 Example 2: Updating Multiple Rows...............................................................................14 DELETING RECORDS....................................................................................................14 Example 1: Deleting a Specific Record............................................................................15

Example 2: Deleting Using an “And” condition in Different Columns.................................15 Example 3: Deleting Using an “OR” condition in Different Columns..................................15 Example 4: Deleting Using the IN Operator in the Same Column.....................................15 JOINING TWO TABLES.................................................................................................16 Inner Joins .....................................................................................................................16 Example 1: Listing Data from Two Tables – Using Full Table Names...............................17 Example 2: Listing Data from Two Tables – Using Variable Names.................................17 Example 3: Linking Two Tables Based on a Range of Values..........................................17 Example 4: Using Greater Than, Less Than, etc. ............................................................18 JOINING MORE THAN TWO TABLES – INNER JOINS.............................................19 Example 1: Joining 3 Tables (Using the Old Syntax) ........................................................19 The New Syntax............................................................................................................20 New Syntax for Two Tables:..........................................................................................20 Example 1: New Syntax for Linking Two Tables.............................................................20 New Syntax for Three Tables:........................................................................................21 Example 2 – New Syntax for Three Tables.....................................................................21 New Syntax for Four Tables ..........................................................................................22 Example 3 – New Syntax for Joining Four Tables...........................................................22 OUTER JOINS.................................................................................................................23 Left Join Syntax: Access.................................................................................................23 Left Join Syntax: Oracle .................................................................................................23 Example 1: Left Join Syntax (Access).............................................................................24 RIGHT Join Syntax: Access...........................................................................................24 Right Outer Join Syntax: Oracle......................................................................................24 Example 1: Right Join Syntax (Access) ...........................................................................25 UNIONS ..........................................................................................................................25 UNION Syntax:.............................................................................................................25 Example 1: Unions .........................................................................................................25

Marshall School of Business

University of Southern California

INTRODUCTION SQL (Structured Query Language) is a computer language that is used to extract data in a relational database. SQL is relatively easy to work with and is commonly used in hundreds of software products including Oracle, MS Access, FoxPro, Dbase, etc. SQL is what is known as a “Declarative” language in that when using it, you specify the end result that you would like rather then the how to get the end result such as you do with “Procedural” languages such as Java and C++. For those of you using Access, when you construct a query by filling out the Access query grid, you are actually generating SQL. If after constructing your Access query, if you go into “SQL View”, you will see the SQL generated. This is an excellent method of teaching yourself SQL.

DISPLAYING INFORMATION FROM A TABLE For the next few examples, the table below (Movies) will be used. ID Production_Co

Movie

Rating

Category

Profit

Released

Suspense

$87,685,700.00

1/1/1990

Adventure

$115,500,000.00

3/7/1989

29 Warner Brothers Batman

Science Fiction $150,500,000.00

1/1/1989

30 United Artists

Drama

$86,813,000.00

7/4/1988

31 20th Century Fox The Princess Bride

Comedy

32 Universal

Back to the Future

Adventure

33 Disney

The Rescuers Down Under

Family

34 Paramount

Beverly Hills Cop

Comedy

$108,000,000.00 11/12/1984

35 Columbia

Ghostbusters

Comedy

$132,720,000.00

1/1/1984

36 Disney

The Jungle Book

Family

$85,900,800.00

1/1/1984

37 20th Century Fox Return of the Jedi

Science Fiction $169,193,000.00

11/1/1983

38 United Artists

The Jerk

Comedy

$54,900,890.00

1/7/1983

39 Universal

E.T.

Science Fiction $228,618,939.00

6/4/1982

40 Paramount

Raiders of the Lost Ark

Adventure

$115,598,000.00

4/7/1981

41 20th Century Fox The Empire Strikes Back

Science Fiction $141,672,000.00

1/1/1980

42 Disney

Charlotte's Web

Family

$108,907,670.00

7/8/1978

43 Disney

101 Dalmations

Family

$198,780,980.00

3/3/1978

44 Paramount

Grease

Comedy

$96,300,000.00

3/2/1978

45 20th Century Fox Star Wars

Science Fiction $193,777,000.00

12/1/1977

46 Columbia

Close Encounters of the Third Kind

Science Fiction

47 Paramount

The Bad News Bears

Comedy

48 Disney

Fantasia

Family

49 Universal

Jaws

Adventure

$129,549,325.00

4/7/1975

50 Disney

Peter Pan

Family

$134,908,600.00

1/1/1974

51 Warner Brothers The Exorcist

Suspense

$89,000,000.00

4/7/1973

52 Disney

Dumbo

Family

$109,890,890.00

7/2/1972

53 Paramount

The Godfather

Drama

$86,275,000.00

5/7/1972

Rain Man

$67,574,560.00

1/1/1987

$105,496,267.00

6/8/1985

$76,570,999.00

5/5/1985

$82,750,000.00

9/8/1977

$70,395,600.00

4/7/1976

$289,767,800.00

1/1/1976

Proper Table Construction Field names should not contain spaces. Access will let you get away with spaces by placing square braces [ ] around the names and Oracle uses double quotes â&#x20AC;&#x153; â&#x20AC;&#x153; but spaces in names will tend to cause more problems than they are worth. The underscore _ is commonly used in place of a space. Table names should not contain spaces. Again, Access will let you get away with spaces through use of square braces [ ] but the underscore is preferred.

SQL-Beginning.doc

September 22, 2000

Page 5

Marshall School of Business

University of Southern California

THE “SELECT” STATEMENT The “Select” statement allows you to display a specified number of columns and rows from a single table. The syntax of the “Select Statement” is shown below. SELECT and FROM must be used and the clauses are optional. The final statement must end in a semicolon (;). SELECT FROM WHERE ORDER BY

List column(s) to display, separated by commas. The name of the table the columns are in. The rows to display. How the rows are to be sorted: (ASC (the default) and descending is DESC.

Example 1: Select Columns, Specify a Criteria, & Sort Ascending This example will display the Production_Co, Movie, and Rating from a table called “Movies”; but only PG rated movies will be displayed. The names of the fields are separated by commas. Text criteria must be surrounded by quotes ‘ ‘ . The entire statement must end with a semicolon (;).

SELECT Production_Co,Movie,Rating FROM Movies WHERE Rating = ‘PG’ ORDER BY Movie;

Example 2: Sorting In Descending Order To sort in descending order (Z-A), add DESC at the end of the ORDER BY Movie line. SELECT Production_Co,Movie,Rating FROM Movies WHERE Rating = ‘PG’ ORDER BY Movie DESC;

SELECT Production_Co,Movie,Rating, Profit FROM Movies WHERE Rating = ‘PG’ ORDER BY Production_Co ASC, Profit DESC;

Example 3 – Selecting All Columns Should you wish to display all of the column, use the apostrophe *. SELECT * FROM Movies WHERE Profit > 100000000

SQL-Beginning.doc

Note that only the SELECT and FROM lines are absolutely necessary when using the “Select” statement.

September 22, 2000

Page 6

Marshall School of Business

University of Southern California

Example 4 – Hiding Duplicate Records (SELECT DISTINCT) SELECT DISTINCE Production_Co FROM Movies

This would produce just one column and list each production company just once.

Example 5 – Hiding Duplicate Records (SELECT DISTINCT) SELECT DISTINCE Production_Co,Rating FROM Movies

This would produce two columns. A production company may be listed more than once because there are three different ratings possible. To be a duplicate, both columns must match.

SPECIFYING CRITERIA This section addresses the “Where” clause of the “Select” statement to set criteria. OPERATOR DESCRIPTION EXAMPLE = Equal to Profit = 100000000.00 Rating = ‘G’ Release = ’04-feb-92’ < Less than Profit < 90000000 <= Less than or equal to Release <= ’04-feb-92’ > Greater than Release > ’04-feb-92’ >= Greater than or equal to Profit >= 100000 <> Not equal to Production_Co <> Disney in Contained in a set Movie in (‘Alien’,’Perfect Storm’) not in Not contained in a set Movie not in (‘Alien’,’Perfect Storm’) between Within a range inclusively Profit between 100000 and 200000 not between Not within the range Profit not between 100000 and 200000 like Used with wild cards Not like Is null Searches for blanks Movie Is Null not is null Is not blank Movie Not Is Null Or And Not

Example 1: Using a Range Criteria This example will only display movies which made over 100 Million. SELECT Production_Co,Movie,Profit FROM Movies WHERE Profit > 100000000 SQL-Beginning.doc ORDER BY Profit DESC;

Note that quotes are not necessary around number fields and commas and dollar signs are not2000 permitted but decimals are allowed. Page 7 September 22,

Marshall School of Business

SQL-Beginning.doc

University of Southern California

September 22, 2000

Page 8

Marshall School of Business

University of Southern California

Example 2: And Conditions Between Different Fields This will find all movies by 20th Century Fox that are rated PG. SELECT Movies.Production_Co, Movies.Rating FROM Movies WHERE Production_Co=’20th Century Fox’ AND Rating=’PG’;

Example 3: Range BETWEEN two Points (Inclusive) This example will only display movies which made between 100 million and 200 million inclusively. SELECT Production_Co,Movie,Profit FROM Movies WHERE Profit between 100000000 and 200000000 ORDER BY Profit DESC;

Note that: WHERE Profit >= 100000000 and <= 100000000 does not work.

Example 4: Range Between two Points (Exclusive) This example will only display movies which made between 100 million and 200 million exclusively. SELECT Production_Co,Movie,Profit FROM Movies WHERE Profit > 100000000 and Profit < 200000000 ORDER BY Profit DESC;

Note that: WHERE Profit >= 100000000 and <= 100000000 does not work.

Example 5: An OR Condition We would like to see all movies by Disney and Paramount. SELECT Movies.Production_Co, Movies.Movie FROM Movies WHERE Production_Co =’disney’ OR Production_Co=’paramount’;

Example 6: Using the Not Operator In this example we wish to see all Movies except those made by Disney and Paramount SELECT Production_Co,Movie FROM Movies SQL-Beginning.doc WHERE NOT Production_Co = ‘Disney;

September 22, 2000

Page 9

Marshall School of Business

University of Southern California

Example 7: Using the <> Operator Same example as above, but using the <> operator rather than “NOT”. SELECT Movies.Production_Co, Movies.Movie FROM Movies WHERE Production_Co <> ‘Disney’;

Note that: WHERE Production_Co Not ‘Disney’ does not work.

Example 8: Excluding Two Criteria We wish to see all production companies except Disney and Paramount. SELECT Movies.Production_Co, Movies.Movie FROM Movies WHERE NOT Production_Co =’disney’ And Not Production_Co=‘paramount’;

Example 9: Searching for Blanks SELECT Movies.Production_Co, Movies.Movie FROM Movies WHERE Production_Co Is Null;

Example 10: Excluding Blanks SELECT Movies.Production_Co, Movies.Movie FROM Movies WHERE NOT Production_Co Is Null;

Example 11: LIKE (Using Wild Cards) The Like operator can only be used in TEXT fields. Further, Access uses the * and Oracle uses the % sign to represent multiple characters. In this example, we will find all movies that start with the letter “A”. SELECT Movies.Production_Co, Movies.Movie FROM Movies WHERE Movie LIKE ‘A*’; SQL-Beginning.doc

September 22, 2000

Note the use of single quotes ‘ ‘. Double quotes do not work.

Page 10

Marshall School of Business

University of Southern California

Example 12: Using the IN Operator with a Number Field The IN operator can be used with Text, Number, or Date fields and is used to check for inclusion in a set. It serves the same purpose as the OR operator but usually requires less typing. This example lists movies whose profit is 100,100,000 or 102,000,000. SELECT Production_Co, Movie, Profit FROM Movies Where profit In (100100000,102000000);

Example 13: Using the IN Operator with a Text Field SELECT Movie, Rating FROM Movies Where Rating In (‘R’,’G’);

SQL-Beginning.doc

September 22, 2000

Page 11

Marshall School of Business

University of Southern California

CREATE A NEW TABLE FROM A QUERY Example 1: Create a New Table from a Query – Standard SQL In standard SQL, there is a CREATE TABLE clause that is added prior to the SELECT statement. Its syntax is: CREATE TABLE Name of New Table AS SELECT FROM WHERE ORDER BY We will create a new table called “Paramount_Moives”. CREATE TABLE Paramount_Movies AS SELECT Production_Co, Movie, Rating, Profit, Release FROM Movies WHERE Production_Co = ‘Paramount’ ORDER BY Movie;

Example 2: Create a New Table from a Query– Access SQL Access uses an “INTO” clause rather than the standard SQL shown above. SELECT Production_Co, Movie, Rating INTO Paramount FROM Movies WHERE Production_Co = 'Paramount';

SAVING A QUERY To save a query in Access, the menus, macros, or modules must be used. The SQL for Oracle is shown below. Standard SQL uses a CREATE VIEW clause before the SELECT statement.

SQL-Beginning.doc

September 22, 2000

Page 12

Marshall School of Business

University of Southern California

Example 1: Saving a Query (or VIEW in SQL) – Standard SQL This example will save the query statement to a view we will name “Rated_G”. CREATE VIEW Rated_G AS SELECT Production_Co, Movie, Rating FROM Movies WHERE Rating = ‘G’;

Deleting a table – “drop table” This command will completely delete an entire table and works in both Oracle and Access DROP TABLE Name_of_Table;

Deleting a view (query) – “drop veiw” This command does not work in Access but does in Oracle and deletes a view (query): DROP VIEW Name_of_View;

MODIFY DATA IN A TABLE Example 1: Adding a New Record – Every Column When using this method, you must list every field in the table in order. The syntax is below: INSERT INTO Name_of_Table VALUES (field1 value, field2 value, …); To add a new row to the “Movies” table:

Note the double quotes surrounding “Heaven’s Shadow”. This is done because the apostrophe in the move’s name would cause an error if the double quotes were absent. “null” allows you to enter a null value in a field.

INSERT INTO Movies VALUES (55,'Addams',"Heaven's Shadow",null,'Suspense',100000000,#1/1/2000#); Example 2: Adding a New Record – Selected Columns This method sometimes preferred when just a few columns are to be filled or one of the fields to be filled is an “Autonumber” field such as Movie_ID. INSERT INTO Name_of_Table (FieldX, FieldY,FieldZ,…) SQL-Beginning.doc

September 22, 2000

Page 13

Marshall School of Business

University of Southern California

VALUES (FieldX value, FieldY value, FieldZ value,…) To create a new record with just the name of the Production Company and Movie, the code below would be used.

INSERT INTO Movies (Production_Co, Movie) VALUES ('Addams',”Heaven’s Shadows”);

EDITING EXISTING RECORDS You can edit one or several columns of a row using the method shown below. You can also update fields with calculations. Note that if you were to leave off the WHERE clause, all rows would be updated. The syntax is below. UPDATE Name_of_Table SET Name_of_Field = New Value or a mathematical operation, Other Column = New Value,… WHERE criteria;

Example 1: Updating a Specific Record with a Value In this example, for the movie “Back to the Future”, we will change the category from Adventure to Family and add 25 million to the existing profit. UPDATE Movies SET Category = 'Family', Profit = Profit + 25000000 Where Movie = 'Back to the Future';

Example 2: Updating Multiple Rows In this example, we wish to change all “R” rated movies to NC-17. UPDATE Movies SET Rating = ‘NC-17’ Where Rating = 'R';

DELETING RECORDS The syntax for deleting one or more records from a table is shown below: SQL-Beginning.doc

September 22, 2000

Page 14

Marshall School of Business

University of Southern California

DELETE FROM Name_of_Table WHERE criteria

Example 1: Deleting a Specific Record In this example, we will delete the record containing the movie “Alien 3”. DELETE FROM Movies WHERE Movie = ‘Alien 3’;

Example 2: Deleting Using an “And” condition in Different Columns In this example, we wish to delete all “G” rated “Paramount” movies. DELETE FROM Movies WHERE Production_Co = 'Paramount' AND Rating = 'G';

Example 3: Deleting Using an “OR” condition in Different Columns In this example, we are using the OR operator to delete any movies made by Disney or whose category is Family. DELETE FROM Movies WHERE Production_Co = 'Disney' or Category = 'Family’;

Example 4: Deleting Using the IN Operator in the Same Column Here, we are using the IN operator to delete all movies by MGM or ORION. We could have used the OR operator as we did above but this is shorter. DELETE FROM Movies WHERE Production_Co IN('MGM','Orion');

SQL-Beginning.doc

September 22, 2000

Page 15

Marshall School of Business

University of Southern California

JOINING TWO TABLES Inner Joins An inner join is characterized by the fact that when more than one table is joined, only records that have a match in the joining field of the other table are shown. Employees Emp_ID 555 444 233

F_Name Jon Wes David

Children Emp_ID 555 555 233

Child_Name Billy June Kevin

L_Name Knocks Clark Philips

Hire_D 1/1/2000 3/7/82 7/9/96

DOB 6/8/88 7/8/92 1/4/97

Gender Male Female Male

For example, suppose you have a table called “Employees ” that lists your employees. You have a second table called ”Children” that lists the children of your employees. The two tables are linked through the common field of “Employee_ID”. When doing an “Inner Join” query that pulls fields from either table, employee Wes Clark (444) will not display because he does not have a matching Emp_ID number in the Children table.

Syntax Structure 1 for an Inner Join Query SELECT

FROM WHERE ORDER BY

Table_Name X.Column_Name from X, Table_Name X.Column_Name from X, Table_Name Y.Column_Name from Y Name of a Table Involved (X) Name of the Other Table Involved (Y) Table_Name(X).Linking_Field = Table_Name (Y).Linking_Field

Syntax Structure 2 for an Inner Join Query SELECT

FROM WHERE ORDER BY

Variable X.Column_Name from X, Variable X.Column_Name from X, Variable Y.Column_Name from Y Name of a Table Involved Variable Name Name of the Other Table Involved Variable Name Table_Name(X).Linking_Field = Table_Name (Y).Linking_Field

SQL-Beginning.doc

September 22, 2000

Page 16

Marshall School of Business

University of Southern California

Example 1: Listing Data from Two Tables – Using Full Table Names This example displays First and Last name from the Employees table and Child_Name and Gender from the Children table.

SELECT Employees.First, Employees.Last, Children.Child_Name, Children.Gender FROM Employees,Children WHERE Employees.Emp_ID = Children.Emp_ID; Example 2: Listing Data from Two Tables – Using Variable Names In this example, variable names are given to the tables (E and C).

E & C represent the tables Employees and Children.

SELECT E.First, E.Last, C.Child_Name, C.Gender FROM Employees E,Children C E & C are defined on the FROM line. WHERE E.Emp_ID = C.Emp_ID;

WHERE links the two tables through their common fields. (E & C still used.)

Example 3: Linking Two Tables Based on a Range of Values The “Movies” table shows the profit made by each movie. Shown below is the “Status” table which categorizes a movies status based on its profit. In this example, there are not two linking fields but three and we are not looking for an exact match but only one where a movies profit falls between a given HIGH and LOW. High $999,999,999.00 $499,999,999.00 $199,999,999.00 $99,999,999.00 $59,999,999.00

Low

Status

$500,000,000.00 $200,000,000.00 $100,000,000.00 $60,000,000.00 $0.00

Mega Blockbuster Blockbuster Hit OK Bomb

SELECT M.Movie, M.Profit, S.Status

FROM Movies M, Status S WHERE M.Profit BETWEEN S.Low AND S.High; A partial list of the result Movie Fried Green Tomatoes SQL-Beginning.doc

Profit Status $50,879,690.0 Bomb September 22, 2000

Page 17

Marshall School of Business

University of Southern California

Four Weddings and a Home Alone 2 Deceived Backdraft

$73,625,300.0 OK $102,000,000. Hit $45,200,389.0 Bomb $987,600,940. Mega Blockbuster

Example 4: Using Greater Than, Less Than, etc. The following operators can also be used: >, <, >=, <= In this example, there are two tables. The first is the Movies table and the other is called “Planet_B” which has the same columns as the Movies table but only contains records for a company called by “Planet BMovie”. We only wish to see which movies made by Planet B did better than out of Movies. Planet_B Movie

Profit

Rating

The Hand that Holds the Remote $10,000,000.00 PG The Sun Also Sets

$30,000,000.00 PG

Went with the Wind

$55,000,000.00 G

The Wizard of Ozzie and Harriet

$61,000,000.00 G

SELECT M.Movie, M.Profit, B.Movie, B.Profit FROM Movies M, Planet_B B WHERE B.Profit > M.Profit;

The Result M.Movie

M.Profit

B.Movie

B.Profit

Fried Green Tomatoes

$50,879,690.00 Went with the Wind

$55,000,000.00

Fried Green Tomatoes

$50,879,690.00 The Wizard of Ozzie and Harriet

$61,000,000.00

Deceived

$45,200,389.00 Went with the Wind

$55,000,000.00

Deceived

$45,200,389.00 The Wizard of Ozzie and Harriet

$61,000,000.00

Honey, I Blew Up the Kids $58,767,800.00 The Wizard of Ozzie and Harriet

$61,000,000.00

The Commitments

$49,876,300.00 Went with the Wind

$55,000,000.00

The Commitments

$49,876,300.00 The Wizard of Ozzie and Harriet

$61,000,000.00

The Jerk

$54,900,890.00 Went with the Wind

$55,000,000.00

The Jerk

$54,900,890.00 The Wizard of Ozzie and Harriet

$61,000,000.00

SQL-Beginning.doc

September 22, 2000

Page 18

Marshall School of Business

University of Southern California

JOINING MORE THAN TWO TABLES – INNER JOINS Using the older syntax, this is not different than joining two tables except that the where condition contains an “AND” operator which is used to set up the multiple links between tables. This old syntax works in both Oracle and Access. The newer syntax (shown in example 2) is only supported by Access at this point.

Example 1: Joining 3 Tables (Using the Old Syntax) In this example, we are linking three tables: Orders, Employees, and Customers. Orders links to Customers through the Customer_ID field and Orders links to Employees through the Employee_ID field.

Employees Employee_ID PP1 PP1 JL1

First_Name Phyllis Phyllis Janice

Customers Customer_ID FRUGF MERRG FOODI

Last_Name Peacock Peacock Leverling

Company_Name Frugal Feast Comestibles Merry Grape Wine Merchants Foodmongers, Inc.

Orders Order_ID 1 2 3

SELECT O.Order_ID, O.Customer_ID, O.Employee_ID, E.First_Name, E.Last_Name, C.Company_Name FROM Orders O, Customers C, Employees E WHERE O.Customer_ID = C.Customer_ID AND O.Employee_ID = E.Employee_ID;

Employee_ID PP1 PP1 JL1

Customer_ID FRUGF MERRG FOODI

The end result. Order_ID Customer_ID Employee_ID First_Name Last_Name

Company_Name

1 FRUGF

PP1

Phyllis

Peacock

Frugal Feast Comestibles

2 MERRG

PP1

Phyllis

Peacock

Merry Grape Wine Merchants

3 FOODI

JL1

Janice

Leverling

Foodmongers, Inc.

4 SILVS

JL1

Janice

Leverling

Silver Screen Food Gems

5 VALUF

JL1

Janice

Leverling

ValuMax Food Stores

6 WALNG

ND1

Nancy

Davolio

Walnut Grove Grocery

SQL-Beginning.doc

September 22, 2000

Page 19

Marshall School of Business

University of Southern California

The New Syntax In the new syntax (supported only by Access thus far), the tables are linked in the FROM clause of the SELECT statement. Further, an ON clause is added, there is no comma after the first table, and the words INNER JOIN appear in front of the second table.

New Syntax for Two Tables:

SELECT Table_Alias.FieldName, Table_Alias.FieldName, Table_Alias.FieldName

FROM FirstTableName AliasName

INNER JOIN SecondTableName AliasName ON FirstTable_Alias.LinkingField = SecondTable_AliasAlias2.LinkingField;

Example 1: New Syntax for Linking Two Tables In this example, a table called “Employees”, which contains a list of employee information, is linked to a table called “Children”, which lists the names of the employee’s children.

SELECT E.Employee_ID, E.First_Name, E.Last_Name, C.Name, C.Gender

FROM Employees E

INNER JOIN Children C ON E.Employee_ID = C.Employee_ID;

SQL-Beginning.doc

September 22, 2000

Page 20

Marshall School of Business

University of Southern California

New Syntax for Three Tables:

SELECT Table_Alias.FieldName, Table_Alias.FieldName, Table_Alias.FieldName

FROM

(Table1Name AliasName INNER JOIN Table2Name AliasName

ON Table2_Alias.LinkingField = Table1_Alias.LinkingField) INNER JOIN Table3Name AliasName ON Table1or2_Alias.LinkingField = Table3_Alias.LinkingField;

Example 2 â&#x20AC;&#x201C; New Syntax for Three Tables This example joins three tables: Orders, Customers, and Employees. Orders links to Customers through Customer_ID and Orders links to Employees through Employee_ID. SELECT O.Order_ID, O.Customer_ID, O.Employee_ID, E.First_Name, E.Last_Name, C.Company_Name FROM (Orders O INNER JOIN Customers C ON O.Customer_ID = C.Customer_ID) INNER JOIN Employees E ON O.Employee_ID = E.Employee_ID;

SQL-Beginning.doc

September 22, 2000

Page 21

Marshall School of Business

University of Southern California

New Syntax for Four Tables

SELECT Table_Alias.FieldName, Table_Alias.FieldName, Table_Alias.FieldName

FROM

((Table1Name AliasName INNER JOIN Table2Name AliasName

ON Table2_Alias.LinkingField = Table1_Alias.LinkingField) INNER JOIN Table3Name AliasName

ON Table1or2_Alias.LinkingField = Table3_Alias.LinkingField) INNER JOIN Table4Name AliasName

ON Table1,2 or 3_Alias.LinkingField = Table4_Alias.LinkingField; Example 3 â&#x20AC;&#x201C; New Syntax for Joining Four Tables In this example, the following tables are joined: EMPLOYEES Employee_ID

ORDERS Order_ID Employee_ID Customer_ID

ORDER_DETAILS Order_ID Candy_ID Quantity

PRODUCTS Candy_ID Brand_Name

SELECT Employees.Last_Name, Products.Brand_Name, Order_Detail.Quantity

FROM ((Products INNER JOIN Order_Detail ON Products.CANDY_ID = Order_Detail.Candy_ID)

INNER JOIN Orders ON Order_Detail.Order_ID = Orders.Order_ID)

INNER JOIN Employees ON Orders.Employee_ID = Employees.Employee_ID; SQL-Beginning.doc

September 22, 2000

Page 22

Marshall School of Business

University of Southern California

OUTER JOINS Inner Joins between tables will only display a row from either table if the linking field finds matching data in each table. An Outer Join is used to display a row from the table even if there is no matching data in the linking field. There are three types of Outer Joins: Right Join: Unmatched rows are displayed in the first table but not displayed in the second. Left Join: Unmatched rows are displayed in the second table but not displayed in the first. Full Join: Unmatched rows are displayed in both tables (Neither Access nor Oracle support this directly). Why use an Outer Join? Suppose you have a table called Employees that lists all of your employees and another table called Children that lists of the children of your employees. The two tables are linked by the common field of Employee_ID. What if you would like a list of employees that do not have any children? In other words, which employees do not have a matching Employee_ID in the Children table.

Left Join Syntax: Access Access uses the newer SQL syntax:

SELECT TableAlias.FieldName, TableAlias.FieldName, TableAlias.FieldName, etc.

FROM Table_Showing_All_Rows Alias

LEFT JOIN Table_Showing_Only_Matching_Rows Alias ON All_Rows_TableAlias.LinkingField = Only_Matching_Rows_Table_Alias.LinkingField;

Left Join Syntax: Oracle Oracle uses the older SQL syntax where (+) at the end indicates a left outer join.

SELECT TableAlias.FieldName, TableAlias.FieldName, TableAlias.FieldName, etc.

FROM Table_Showing_All_Rows Alias, Table_Showing_Only_Matching_Rows Alias

WHERE All_Rows_TableAlias.LinkingField = Only_Matching_Rows_Table_Alias.LinkingField (+);

SQL-Beginning.doc

September 22, 2000

Page 23

Marshall School of Business

University of Southern California

Example 1: Left Join Syntax (Access) In this example, we wish to see all records from “Employees” and only matching records from “Children”. The tables link through the Employee_ID field.

SELECT E.Employee_ID, E.First_Name, E.Last_Name, C.Name, C.Gender

FROM Employees E

LEFT JOIN Children C ON E.Employee_ID = C.Employee_ID;

RIGHT Join Syntax: Access The only grammatical difference is that the term RIGHT JOIN is used rather then LEFT JOIN and that the table to display all rows is listed last. When performing an Outer Join with just two tables, you can use LEFT or RIGHT join in the syntax as long as you pay attention to which table is listed first and which is listed second.

SELECT TableAlias.FieldName, TableAlias.FieldName, TableAlias.FieldName, etc.

FROM Table_Showing_Only_Matching_Rows Alias RIGHT JOIN Table_Showing_All_Rows Alias ON Only_Matching_Rows_Table_Alias.LinkingField = All_Rows_TableAlias.LinkingField;

Right Outer Join Syntax: Oracle Oracle uses the older SQL syntax where (+) at the end indicates a left outer join.

SELECT TableAlias.FieldName, TableAlias.FieldName, TableAlias.FieldName, etc.

FROM Table_Showing_All_Rows Alias, Table_Showing_Only_Matching_Rows Alias

WHERE All_Rows_TableAlias.LinkingField (+) = Only_Matching_Rows_Table_Alias.LinkingField;

SQL-Beginning.doc

September 22, 2000

Page 24

Marshall School of Business

University of Southern California

Example 1: Right Join Syntax (Access) This uses the same tables as example 1.

SELECT E.Employee_ID, E.First_Name, E.Last_Name, C.Name, C.Gender

FROM Children C RIGHT JOIN Employees E ON C.Employee_ID = E.Employee_ID;

UNIONS In the other queries we have performed, when you display fields from different tables, the data appears in separate columns; with UNIONs, the data from different tables is placed in the same columns. For example, in an inner join, if you select 2 fields from one table and 2 from another, you get 4 columns, with a UNION join, you get two. For this to function properly, the two tables must have the same structure and field types. Note that duplicate rows are not displayed when using UNION. (To display duplicate rows, use “UNION ALL”) Note also that UNIONS take a large amount of CPU time to run.

UNION Syntax: SELECT

The * can be used to display all columns rather than naming them individually.

Table1_Alias.*

FROM Table1Name Table1_Alias

UNION SELECT Table2_Alias.* FROM Table2Name Table2_Alias;

Example 1: Unions In this example, there is a table called Customers which lists all customers from North America. There is another table called UK_People which lists customers from the UK. Both tables have the exact same structure. The SQL to produce a UNION is below.

SELECT UK.* FROM UK_People UK UNION SELECT C.* FROM Customers C; SQL-Beginning.doc

September 22, 2000

Page 25

Marshall School of Business

SQL-Beginning.doc

University of Southern California

September 22, 2000

Page 26