Mega Code Archive
Correlated subquery using the department table in both inner and outer queries
<![CDATA[
1>
2> CREATE TABLE department(dept_no CHAR(4) NOT NULL,
3> dept_name CHAR(25) NOT NULL,
4> location CHAR(30) NULL)
5>
6> insert into department values ('d1', 'developer', 'Dallas')
7> insert into department values ('d2', 'tester', 'Seattle')
8> insert into department values ('d3', 'marketing', 'Dallas')
9>
10> select * from department
11> 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> -- Correlated subquery using the department table in both inner and outer queries
2>
3> SELECT t1.*
4> FROM department t1
5> WHERE t1.location IN
6> (SELECT t2.location
7> FROM department t2
8> WHERE t1.dept_no <> t2.dept_no)
9> GO
dept_no dept_name location
------- ------------------------- ------------------------------
d1 developer Dallas
d3 marketing Dallas
(2 rows affected)
1>
2> drop table department
3> GO
1>
]]>