Joining a Table with Itself : Table Join « Table Joins « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Table Joins
3. Table Join

Joining a Table with Itself

14>
15> CREATE TABLE department(dept_no   CHAR(4) NOT NULL,
16>                         dept_name CHAR(25) NOT NULL,
17>                         location  CHAR(30) NULL)
18>
19> insert into department values ('d1', 'developer',   'Dallas')
20> insert into department values ('d2', 'tester',      'Seattle')
21> insert into department values ('d3', 'marketing',  'Dallas')
22>
23> select * from department
24> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)
dept_no dept_name                 location
------- ------------------------- ------------------------------
d1      developer                 Dallas
d2      tester                    Seattle
d3      marketing                 Dallas

(3 rows affected)
1> -- Joining a Table with Itself
2>
3> -- ANSI join syntax:
4>
5> SELECT t1.dept_no, t1.dept_name, t1.location
6>          FROM department t1 JOIN department t2
7>            ON t1.location=t2.location
8>          WHERE t1.dept_no <> t2.dept_no
9> GO
dept_no dept_name                 location
------- ------------------------- ------------------------------
d3      marketing                 Dallas
d1      developer                 Dallas

(2 rows affected)
1> -- SQL Server join syntax:
2>
3> SELECT DISTINCT t1.dept_no, t1.dept_name, t1.location
4>        FROM department t1, department t2
5>        WHERE t1. location = t2.location
6>        AND t1.dept_no <> t2.dept_no
7> GO
dept_no dept_name                 location
------- ------------------------- ------------------------------
d1      developer                 Dallas
d3      marketing                 Dallas

(2 rows affected)
1>
2> drop table department
3> GO
1>
2>
           
       

Related examples in the same category