Matching Couples Using a Cross Join; Couples with Different Genders : Cross Join « Table Join « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Table Join
3. Cross Join

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)