SQL Server/T-SQL Tutorial/Table Join/Cross Join
Содержание
Cross Join with itself
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
it is the Cartesian product of all the rows from all tables participating in the SELECT statement
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
Matching Couples Using a Cross Join; Couples with Different Genders
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)
SQL-92 Cross Join Syntax
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>