SQL Server/T-SQL Tutorial/Table Join/Table Join
Содержание
- 1 A SELECT statement that joins the Bankers and Billings tables
- 2 Forcing the Order of Join Processing
- 3 Joining Tables in the FROM Clause (ANSI standard)
- 4 Joining Tables in the WHERE Clause (not ANSI standard)
- 5 Joining three tables.
- 6 Joins and Subqueries
- 7 Join tables with two columns
- 8 Left and Right Outer Joins
- 9 Selecting authors and titles using only joins.
- 10 SQL-92 Three-Way Inner Joins
- 11 SQL Server 2005 join types fall into three categories: inner, outer, and cross.
- 12 Table Aliasing
- 13 The result of the previous join is then joined to another table
- 14 Using the GROUP BY Clause
A SELECT statement that joins the Bankers and Billings tables
<source lang="sql">
8> 9> create table Billings ( 10> BankerID INTEGER, 11> BillingNumber INTEGER, 12> BillingDate datetime, 13> BillingTotal INTEGER, 14> TermsID INTEGER, 15> BillingDueDate datetime , 16> PaymentTotal INTEGER, 17> CreditTotal INTEGER 18> 19> ); 20> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> SELECT BillingNumber, BankerName 5> FROM Bankers JOIN Billings 6> ON Bankers.BankerID = Billings.BankerID 7> GO BillingNumber BankerName
--------------------
1 ABC Inc.
2 DEF Inc.
3 HJI Inc.
4 QWE Inc.
5 RTY Inc.
6 YUI Inc.
7 OIP Inc.
8 SAD Inc.
9 DFG Inc.
0 GHJ Inc.
(10 rows affected) 1> 2> drop table Bankers; 3> drop table Billings; 4> GO</source>
Forcing the Order of Join Processing
<source lang="sql">
3> 4> CREATE TABLE Departments( 5> Deptno int NOT NULL CONSTRAINT PK_dept_deptno PRIMARY KEY, 6> deptname varchar(15) NOT NULL 7> ) 8> GO 1> 2> CREATE TABLE Jobs( 3> jobid int NOT NULL CONSTRAINT PK_jobs_jobid PRIMARY KEY, 4> jobdesc varchar(15) NOT NULL 5> ) 6> GO 1> 2> CREATE TABLE Employees( 3> empid int NOT NULL CONSTRAINT PK_emps_empid PRIMARY KEY, 4> empname varchar(10) NOT NULL, 5> deptno int NULL CONSTRAINT FK_emps_depts REFERENCES Departments(deptno), 6> jobid int NOT NULL CONSTRAINT FK_emps_jobs REFERENCES Jobs(jobid), 7> salary decimal(7,2) NOT NULL 8> ) 9> GO 1> 2> INSERT INTO Departments VALUES(100, "sqleing") 3> INSERT INTO Departments VALUES(200, "Production") 4> INSERT INTO Departments VALUES(300, "Marketing") 5> INSERT INTO Departments VALUES(400, "Management") 6> INSERT INTO Jobs VALUES(10, "sqle") 7> INSERT INTO Jobs VALUES(20, "Oracle") 8> INSERT INTO Jobs VALUES(30, "MySQL") 9> INSERT INTO Jobs VALUES(40, "SqlServer") 10> INSERT INTO Employees VALUES(1, "Joe", 400, 30, 3456.00) 11> INSERT INTO Employees VALUES(2, "James", 200, 20, 4325.00) 12> INSERT INTO Employees VALUES(3, "Chris", 100, 10, 8952.00) 13> INSERT INTO Employees VALUES(4, "Rob", 400, 30, 1234.00) 14> INSERT INTO Employees VALUES(5, "Linda", 400, 30, 4567.00) 15> INSERT INTO Employees VALUES(6, "Lisa", NULL, 30, 8765.00) 16> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT 3> empid, 4> empname, 5> salary, 6> E.deptno, 7> deptname, 8> E.jobid, 9> jobdesc 10> FROM 11> Employees AS E 12> JOIN 13> Departments AS D ON E.deptno = D.deptno 14> JOIN 15> Jobs AS J ON E.jobid = J.jobid 16> OPTION(FORCE ORDER) 17> 18> drop table Employees 19> drop table jobs 20> drop table Departments 21> GO empid empname salary deptno deptname jobid jobdesc
---------- --------- ----------- --------------- ----------- ---------------
1 Joe 3456.00 400 Management 30 MySQL
2 James 4325.00 200 Production 20 Oracle
3 Chris 8952.00 100 sqleing 10 sqle
4 Rob 1234.00 400 Management 30 MySQL
5 Linda 4567.00 400 Management 30 MySQL
(5 rows affected) 1></source>
Joining Tables in the FROM Clause (ANSI standard)
<source lang="sql">
7> CREATE TABLE employee( 8> id INTEGER NOT NULL PRIMARY KEY, 9> first_name VARCHAR(10), 10> last_name VARCHAR(10), 11> salary DECIMAL(10,2), 12> start_Date DATETIME, 13> region VARCHAR(10), 14> city VARCHAR(20) 15> ); 16> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> select * from employee; 2> GO id first_name last_name salary start_Date region city
---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected) 1> 2> CREATE TABLE title( 3> id INTEGER, 4> job_title VARCHAR(20) 5> ); 6> GO 1> INSERT INTO title VALUES (1, "developer"); 2> INSERT INTO title VALUES (1, "manager"); 3> INSERT INTO title VALUES (2, "tester"); 4> INSERT INTO title VALUES (2, "programmer"); 5> INSERT INTO title VALUES (3, "boss"); 6> INSERT INTO title VALUES (4, "sales"); 7> INSERT INTO title VALUES (5, "market"); 8> INSERT INTO title VALUES (6, "coder"); 9> INSERT INTO title VALUES (7, "tester"); 10> INSERT INTO title VALUES (8, "developer"); 11> INSERT INTO title VALUES (9, "manager"); 12> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT Employee.First_Name, title.job_title 4> FROM Employee INNER JOIN title 5> ON Employee.ID = title.ID 6> 7> drop table employee; 8> drop table title; 9> GO First_Name job_title
--------------------
Jason developer Jason manager Alison tester Alison programmer James boss Celia sales Robert market Linda coder David tester James developer Joan manager (11 rows affected) 1> 2></source>
Joining Tables in the WHERE Clause (not ANSI standard)
<source lang="sql">
5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20) 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> select * from employee; 2> GO id first_name last_name salary start_Date region city
---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected) 1> 2> CREATE TABLE title( 3> id INTEGER, 4> job_title VARCHAR(20) 5> ); 6> GO 1> INSERT INTO title VALUES (1, "developer"); 2> INSERT INTO title VALUES (1, "manager"); 3> INSERT INTO title VALUES (2, "tester"); 4> INSERT INTO title VALUES (2, "programmer"); 5> INSERT INTO title VALUES (3, "boss"); 6> INSERT INTO title VALUES (4, "sales"); 7> INSERT INTO title VALUES (5, "market"); 8> INSERT INTO title VALUES (6, "coder"); 9> INSERT INTO title VALUES (7, "tester"); 10> INSERT INTO title VALUES (8, "developer"); 11> INSERT INTO title VALUES (9, "manager"); 12> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT employee.First_Name, title.job_title 4> FROM Employee, title 5> WHERE employee.ID = title.ID 6> GO First_Name job_title
--------------------
Jason developer Jason manager Alison tester Alison programmer James boss Celia sales Robert market Linda coder David tester James developer Joan manager (11 rows affected) 1> 2> 3> drop table employee; 4> drop table title; 5> GO 1> 2> 3> 4></source>
Joining three tables.
<source lang="sql">
4> 5> 6> CREATE TABLE titles( 7> title_id varchar(20), 8> title varchar(80) NOT NULL, 9> type char(12) NOT NULL, 10> pub_id char(4) NULL, 11> price money NULL, 12> advance money NULL, 13> royalty int NULL, 14> ytd_sales int NULL, 15> notes varchar(200) NULL, 16> pubdate datetime NOT NULL 17> ) 18> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE titleauthor( 3> au_id varchar(20), 4> title_id varchar(20), 5> au_ord tinyint NULL, 6> royaltyper int NULL 7> ) 8> GO 1> 2> insert titleauthor values("1", "2", 1, 60) 3> insert titleauthor values("2", "3", 1, 100) 4> insert titleauthor values("3", "4", 1, 100) 5> insert titleauthor values("4", "5", 1, 100) 6> insert titleauthor values("5", "6", 1, 100) 7> insert titleauthor values("6", "7", 2, 40) 8> insert titleauthor values("7", "8", 1, 100) 9> insert titleauthor values("8", "9", 1, 100) 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT au_lname, title 4> FROM titles t 5> JOIN titleauthor ta ON ta.title_id = t.title_id 6> JOIN authors a ON a.au_id = ta.au_id 7> WHERE au_lname = "Green" 8> GO au_lname title
--------------------------------------------------------------------------------
(0 rows affected) 1> 2> drop table titles; 3> drop table titleauthor; 4> GO 1></source>
Joins and Subqueries
<source lang="sql">
3> 4> 5> CREATE TABLE titles( 6> title_id varchar(20), 7> title varchar(80) NOT NULL, 8> type char(12) NOT NULL, 9> pub_id char(4) NULL, 10> price money NULL, 11> advance money NULL, 12> royalty int NULL, 13> ytd_sales int NULL, 14> notes varchar(200) NULL, 15> pubdate datetime NOT NULL 16> ) 17> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE sales( 3> stor_id char(4) NOT NULL, 4> ord_num varchar(20) NOT NULL, 5> ord_date datetime NOT NULL, 6> qty smallint NOT NULL, 7> payterms varchar(12) NOT NULL, 8> title_id varchar(80) 9> ) 10> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT titles.title, titles.price 3> FROM titles JOIN sales ON sales.title_id = titles.title_id 4> WHERE sales.qty > 30 5> 6> GO title price
---------------------
Secrets 20.0000 (1 rows affected) 1> 2> drop table sales; 3> drop table titles; 4> GO</source>
Join tables with two columns
<source lang="sql">
3> CREATE TABLE Orders ( 4> OrderID int NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> SELECT CustomerID, MIN(OrderDate) AS OrderDate 3> INTO #MinOrderDates 4> FROM Orders 5> GROUP BY CustomerID 6> ORDER BY CustomerID 7> GO (0 rows affected) 1> SELECT o.CustomerID, o.OrderID, o.OrderDate 2> FROM Orders o 3> JOIN #MinOrderDates t 4> ON o.CustomerID = t.CustomerID 5> AND o.OrderDate = t.OrderDate 6> ORDER BY o.CustomerID 7> GO CustomerID OrderID OrderDate
----------- -----------------------
(0 rows affected) 1> DROP TABLE #MinOrderDates 2> 3> drop table orders; 4> GO</source>
Left and Right Outer Joins
<source lang="sql">
You can start off with an example of a left outer join. Retrieve all titles and their associated quantity of sales. If the title had no sales, you still want to see it listed. 10> 11> 12> CREATE TABLE sales( 13> stor_id char(4) NOT NULL, 14> ord_num varchar(20) NOT NULL, 15> ord_date datetime NOT NULL, 16> qty smallint NOT NULL, 17> payterms varchar(12) NOT NULL, 18> title_id varchar(80) 19> ) 20> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE titles( 3> title_id varchar(20), 4> title varchar(80) NOT NULL, 5> type char(12) NOT NULL, 6> pub_id char(4) NULL, 7> price money NULL, 8> advance money NULL, 9> royalty int NULL, 10> ytd_sales int NULL, 11> notes varchar(200) NULL, 12> pubdate datetime NOT NULL 13> ) 14> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT sales.qty, titles.title 4> FROM titles LEFT OUTER JOIN sales 5> ON titles.title_id = sales.title_id 6> GO qty title
--------------------------------------------------------------------------------
75 Secrets 10 Secrets 20 The 10 The 25 Emotional 15 Emotional NULL Prolonged NULL With NULL Valley NULL Any? NULL Fifty
(11 rows affected) 1> 2> drop table sales; 3> drop table titles; 4> GO 1></source>
Selecting authors and titles using only joins.
<source lang="sql">
5> 6> CREATE TABLE titles( 7> title_id varchar(20), 8> title varchar(80) NOT NULL, 9> type char(12) NOT NULL, 10> pub_id char(4) NULL, 11> price money NULL, 12> advance money NULL, 13> royalty int NULL, 14> ytd_sales int NULL, 15> notes varchar(200) NULL, 16> pubdate datetime NOT NULL 17> ) 18> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE authors( 3> au_id varchar(11), 4> au_lname varchar(40) NOT NULL, 5> au_fname varchar(20) NOT NULL, 6> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 7> address varchar(40) NULL, 8> city varchar(20) NULL, 9> state char(2) NULL, 10> zip char(5) NULL, 11> contract bit NOT NULL 12> ) 13> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE titleauthor( 3> au_id varchar(20), 4> title_id varchar(20), 5> au_ord tinyint NULL, 6> royaltyper int NULL 7> ) 8> GO 1> 2> insert titleauthor values("1", "2", 1, 60) 3> insert titleauthor values("2", "3", 1, 100) 4> insert titleauthor values("3", "4", 1, 100) 5> insert titleauthor values("4", "5", 1, 100) 6> insert titleauthor values("5", "6", 1, 100) 7> insert titleauthor values("6", "7", 2, 40) 8> insert titleauthor values("7", "8", 1, 100) 9> insert titleauthor values("8", "9", 1, 100) 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT au_lname, title 3> FROM authors a JOIN titleauthor ta ON a.au_id = ta.au_id 4> JOIN titles t ON t.title_id = ta.title_id 5> 6> GO au_lname title
--------------------------------------------------------------------------------
Joe The Jack Emotional Pink Prolonged Blue With Red Valley Black Any? White Fifty (7 rows affected) 1> 2> drop table titleauthor; 3> drop table authors; 4> drop table titles; 5> GO 1></source>
SQL-92 Three-Way Inner Joins
<source lang="sql">
3> 4> CREATE TABLE Departments( 5> Deptno int NOT NULL CONSTRAINT PK_dept_deptno PRIMARY KEY, 6> deptname varchar(15) NOT NULL 7> ) 8> GO 1> 2> CREATE TABLE Jobs( 3> jobid int NOT NULL CONSTRAINT PK_jobs_jobid PRIMARY KEY, 4> jobdesc varchar(15) NOT NULL 5> ) 6> GO 1> 2> CREATE TABLE Employees( 3> empid int NOT NULL CONSTRAINT PK_emps_empid PRIMARY KEY, 4> empname varchar(10) NOT NULL, 5> deptno int NULL CONSTRAINT FK_emps_depts REFERENCES Departments(deptno), 6> jobid int NOT NULL, 7> salary decimal(7,2) NOT NULL 8> ) 9> GO 1> 2> INSERT INTO Departments VALUES(100, "sqleing") 3> INSERT INTO Departments VALUES(200, "Production") 4> INSERT INTO Departments VALUES(300, "Marketing") 5> INSERT INTO Departments VALUES(400, "Management") 6> INSERT INTO Jobs VALUES(10, "sqle") 7> INSERT INTO Jobs VALUES(20, "Oracle") 8> INSERT INTO Jobs VALUES(30, "MySQL") 9> INSERT INTO Jobs VALUES(40, "SqlServer") 10> INSERT INTO Employees VALUES(1, "Joe", 400, 30, 3456.00) 11> INSERT INTO Employees VALUES(2, "James", 200, 20, 4325.00) 12> INSERT INTO Employees VALUES(3, "Chris", 100, 10, 8952.00) 13> INSERT INTO Employees VALUES(4, "Rob", 400, 30, 1234.00) 14> INSERT INTO Employees VALUES(5, "Linda", 400, 30, 4567.00) 15> INSERT INTO Employees VALUES(6, "Lisa", NULL, 30, 8765.00) 16> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> 4> SELECT 5> empid, 6> empname, 7> salary, 8> E.deptno, 9> deptname, 10> E.jobid, 11> jobdesc 12> FROM 13> Employees AS E 14> JOIN 15> Departments AS D ON E.deptno = D.deptno 16> JOIN 17> Jobs AS J ON E.jobid = J.jobid 18> 19> drop table Employees 20> drop table jobs 21> drop table Departments 22> GO empid empname salary deptno deptname jobid jobdesc
---------- --------- ----------- --------------- ----------- ---------------
1 Joe 3456.00 400 Management 30 MySQL
2 James 4325.00 200 Production 20 Oracle
3 Chris 8952.00 100 sqleing 10 sqle
4 Rob 1234.00 400 Management 30 MySQL
5 Linda 4567.00 400 Management 30 MySQL
(5 rows affected)</source>
SQL Server 2005 join types fall into three categories: inner, outer, and cross.
<source lang="sql">
Inner joins use the INNER JOIN keywords. INNER JOIN operates by matching common values between two tables. Only table rows satisfying the join conditions are used to construct the result set. INNER JOINs are the default JOIN type. You can use just the JOIN keyword in your INNER JOIN operations. Outer joins have three different join types: LEFT OUTER, RIGHT OUTER, and FULL OUTER joins. LEFT OUTER and RIGHT OUTER JOINs return rows that match the conditions of the join condition. LEFT OUTER JOINs return unmatched rows from the first table of the join pair. RIGHT OUTER JOINs return unmatched rows from the second table of the join pair. The FULL OUTER JOIN clause returns unmatched rows on both the left and right tables. A CROSS JOIN returns a Cartesian product when a WHERE clause is not used. A Cartesian product produces a result set based on every possible combination of rows from the left table, multiplied against the rows in the right table. If the table A has 7 rows, and the table B has 22 rows, you would receive 154 rows (or 7 times 22) in the query results.</source>
Table Aliasing
<source lang="sql">
3> 4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20), 12> managerid INTEGER 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> SELECT ID, First_Name FROM Employee AS P 4> 5> 6> drop table employee; 7> GO ID First_Name
----------
1 Jason
2 Alison
3 James
4 Celia
5 Robert
6 Linda
7 David
8 James
9 Joan
(9 rows affected) 1> 2></source>
The result of the previous join is then joined to another table
<source lang="sql">
3> CREATE TABLE authors( 4> au_id varchar(11), 5> au_lname varchar(40) NOT NULL, 6> au_fname varchar(20) NOT NULL, 7> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 8> address varchar(40) NULL, 9> city varchar(20) NULL, 10> state char(2) NULL, 11> zip char(5) NULL, 12> contract bit NOT NULL 13> ) 14> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE titles( 3> title_id varchar(20), 4> title varchar(80) NOT NULL, 5> type char(12) NOT NULL, 6> pub_id char(4) NULL, 7> price money NULL, 8> advance money NULL, 9> royalty int NULL, 10> ytd_sales int NULL, 11> notes varchar(200) NULL, 12> pubdate datetime NOT NULL 13> ) 14> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE titleauthor( 3> au_id varchar(20), 4> title_id varchar(20), 5> au_ord tinyint NULL, 6> royaltyper int NULL 7> ) 8> GO 1> 2> insert titleauthor values("1", "2", 1, 60) 3> insert titleauthor values("2", "3", 1, 100) 4> insert titleauthor values("3", "4", 1, 100) 5> insert titleauthor values("4", "5", 1, 100) 6> insert titleauthor values("5", "6", 1, 100) 7> insert titleauthor values("6", "7", 2, 40) 8> insert titleauthor values("7", "8", 1, 100) 9> insert titleauthor values("8", "9", 1, 100) 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> 4> SELECT 5> "Author"=RTRIM(au_lname) + ", " + au_fname, 6> "Title"=title 7> FROM 8> ( -- JOIN CONDITIONS 9> -- FIRST join authors and titleauthor 10> (authors AS A 11> FULL OUTER JOIN titleauthor AS TA ON A.au_id=TA.au_id 12> ) 13> -- The result of the previous join is then joined to titles 14> FULL OUTER JOIN titles AS T ON TA.title_id=T.title_id 15> ) 16> WHERE 17> state <> "CA" OR state IS NULL 18> ORDER BY 1 19> GO Author Title
--------------------------------------------------------------------------------
NULL Secrets Black, Michel Any? Black, Michel Any? Black, Michel Any? Black, Michel Any? Blue, Albert With Blue, Albert With Blue, Albert With Blue, Albert With Pink, Cherry Prolonged Pink, Cherry Prolonged Pink, Cherry Prolonged Pink, Cherry Prolonged Red, Anne Valley Red, Anne Valley Red, Anne Valley Red, Anne Valley White, Sylvia Fifty White, Sylvia Fifty White, Sylvia Fifty White, Sylvia Fifty (21 rows affected) 1> 2> 3> drop table authors; 4> drop table titles; 5> drop table titleauthors; 6> GO 1></source>
Using the GROUP BY Clause
<source lang="sql">
4> 5> CREATE TABLE Classification ( 6> Classif_ID integer NOT NULL PRIMARY KEY, 7> Classification varchar(25)) 8> GO 1> 2> INSERT into Classification VALUES( 1,"Pop") 3> INSERT into Classification VALUES( 2,"Country") 4> INSERT into Classification VALUES( 3,"Alternative") 5> INSERT into Classification VALUES( 4,"Metal") 6> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE CD ( 4> CD_ID integer NOT NULL PRIMARY KEY, 5> CD_Title varchar(40), 6> Composer_ID integer NOT NULL, 7> Classif_ID integer NOT NULL, 8> SalesPrice money, 9> AverageCost money) 10> GO 1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99) 2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99) 3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99) 4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99) 5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99) 6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99) 7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99) 8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99) 9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99) 10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99) 11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99) 12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99) 13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99) 14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99) 15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99) 16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99) 17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99) 18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99) 19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99) 20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99) 21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99) 22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99) 23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99) 24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99) 25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99) 26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99) 27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99) 28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99) 29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99) 30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99) 31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99) 32> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT CD.Classif_ID,Classification.Classification, 3> Count(CD.CD_ID) "Total Offerings" 4> FROM CD,Classification 5> WHERE CD.Classif_ID = Classification.Classif_ID 6> GROUP BY CD.Classif_ID,Classification.Classification 7> GO Classif_ID Classification Total Offerings
------------------------- ---------------
1 Pop 19
2 Country 2
3 Alternative 3
4 Metal 7
(4 rows affected) 1> 2> 3> drop table Classification; 4> drop table CD; 5> GO</source>
