SQL Server/T-SQL Tutorial/Table Join/Cross Join — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 13:25, 26 мая 2010

Cross Join with itself

   <source lang="sql">

4> 5> CREATE TABLE Candidates( 6> candname varchar(10) NOT NULL, 7> gender char(1) NOT NULL CONSTRAINT CHK_gender CHECK (gender IN("F", "M")) 8> ) 9> INSERT INTO Candidates VALUES("A", "M") 10> INSERT INTO Candidates VALUES("B", "M") 11> INSERT INTO Candidates VALUES("C", "F") 12> INSERT INTO Candidates VALUES("D", "F") 13> 14> SELECT 15> T1.candname, 16> T2.candname 17> FROM 18> Candidates AS T1 19> CROSS JOIN 20> Candidates AS T2 21> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) candname candname


----------

A A B A C A D A A B B B C B D B A C B C C C D C A D B D C D D D (16 rows affected) 1> 2> drop table candidates 3> GO</source>


it is the Cartesian product of all the rows from all tables participating in the SELECT statement

   <source lang="sql">

8> 9> 10> CREATE TABLE titles( 11> title_id varchar(20), 12> title varchar(80) NOT NULL, 13> type char(12) NOT NULL, 14> pub_id char(4) NULL, 15> price money NULL, 16> advance money NULL, 17> royalty int NULL, 18> ytd_sales int NULL, 19> notes varchar(200) NULL, 20> pubdate datetime NOT NULL 21> ) 22> 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> 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> SELECT au_lname, title 3> FROM authors CROSS JOIN titles 4> GO au_lname title


--------------------------------------------------------------------------------

Joe Secrets Jack Secrets Pink Secrets Blue Secrets Red Secrets Black Secrets White Secrets Yellow Secrets Gold Secrets Siler Secrets Joe The Jack The Pink The Blue The Red The Black The White The Yellow The Gold The Siler The Joe Emotional Jack Emotional Pink Emotional Blue Emotional Red Emotional Black Emotional White Emotional Yellow Emotional Gold Emotional Siler Emotional Joe Prolonged Jack Prolonged Pink Prolonged Blue Prolonged Red Prolonged Black Prolonged White Prolonged Yellow Prolonged Gold Prolonged Siler Prolonged Joe With Jack With Pink With Blue With Red With Black With White With Yellow With Gold With Siler With Joe Valley Jack Valley Pink Valley Blue Valley Red Valley Black Valley White Valley Yellow Valley Gold Valley Siler Valley Joe Any? Jack Any? Pink Any? Blue Any? Red Any? Black Any? White Any? Yellow Any? Gold Any? Siler Any? Joe Fifty Jack Fifty Pink Fifty Blue Fifty Red Fifty Black Fifty White Fifty Yellow Fifty Gold Fifty Siler Fifty (80 rows affected) 1> 2> drop table authors; 3> drop table titleauthor; 4> drop table titles; 5> GO</source>


Matching Couples Using a Cross Join; Couples with Different Genders

   <source lang="sql">

3> 4> CREATE TABLE Candidates( 5> candname varchar(10) NOT NULL, 6> gender char(1) NOT NULL CONSTRAINT CHK_gender CHECK (gender IN("F", "M")) 7> ) 8> INSERT INTO Candidates VALUES("A" , "M") 9> INSERT INTO Candidates VALUES("B" , "M") 10> INSERT INTO Candidates VALUES("C", "F") 11> INSERT INTO Candidates VALUES("D" , "F") 12> 13> 14> SELECT 15> T1.candname, 16> T2.candname 17> FROM 18> Candidates AS T1 19> CROSS JOIN 20> Candidates AS T2 21> WHERE 22> T1.gender <> T2.gender 23> 24> drop table Candidates 25> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) candname candname


----------

C A D A C B D B A C B C A D B D (8 rows affected)</source>


SQL-92 Cross Join Syntax

   <source lang="sql">

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> 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> 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 4> deptname, 5> jobdesc 6> FROM 7> Departments 8> CROSS JOIN 9> Jobs 10> 11> drop table Jobs 12> drop table Departments 13> GO deptname jobdesc


---------------

sqleing sqle Production sqle Marketing sqle Management sqle sqleing Oracle Production Oracle Marketing Oracle Management Oracle sqleing MySQL Production MySQL Marketing MySQL Management MySQL sqleing SqlServer Production SqlServer Marketing SqlServer Management SqlServer (16 rows affected) 1></source>