can any one explain how this query execute

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » can any one explain how this query execute

can any one explain how this query execute

Rate Topic

Display Mode

Topic Options

Author

Message

narendra.kongara

Posted Tuesday, December 11, 2012 1:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 14, 2013 2:20 AM
Points: 2, Visits: 14

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)

Post #1394934

Koen Verbeeck

Posted Tuesday, December 11, 2012 1:59 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 9,373, Visits: 6,470

What's the question?

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1394942

nigelrivett

Posted Tuesday, December 11, 2012 1:59 AM

Old Hand

Group: General Forum Members
Last Login: Monday, January 28, 2013 1:45 AM
Points: 386, Visits: 199

Not sure what you are asking
You can run the following in a query window in management studio and get the output - but will be left with the table in a database

CREATE TABLE department
(
dept_no CHAR(4) NOT NULL,
dept_name CHAR(25) NOT NULL,
location CHAR(30) NULL
)
go
insert into department values ('d1', 'developer', 'Dallas')
insert into department values ('d2', 'tester', 'Seattle')
insert into department values ('d3', 'marketing', 'Dallas')

select * from department

What you have there looks like it was the output from something like osql

Cursors never.
DTS - only when needed and never to control.

Post #1394943

Chris Harshman

Posted Tuesday, December 11, 2012 8:57 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:19 AM
Points: 1,562, Visits: 1,716

narendra.kongara (12/11/2012)

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

The correlated subquery works by executing once for each row in the department table aliased as t1. For each of those records, it sees if the location in the record of t1 exists in the same department table aliased as t2 but with a different dept_no. Since Dallas is the only location with multiple departments in your sample data, it is the only location that satisfies the WHERE clause.

Post #1395172

« Prev Topic | Next Topic »

Permissions