Correlated subquery using the department table in both inner and outer queries : Correlated subquery « Subquery « SQL Server / T-SQL






  1. SQL Server / T-SQL
  2. Subquery
  3. Correlated subquery

Correlated subquery using the department table in both inner and outer queries

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>








Related examples in the same category

1.WHERE clause in the subquery's SELECT statement links the inner query to the outer query
2.Correlated subquery: the inner query depends on the outer query for any of its values
3.Correlated subquery using Distinct
4.Leaf-Level Employees (Employees with No Subordinates), Correlated Subquery Syntax
5.Leaf-Level Employees (Employees with No Subordinates), Join Syntax