Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

"SELECT 1 FROM TABLE" - What this query do?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Ambili S

Active User


Joined: 06 Sep 2007
Posts: 112
Location: India

PostPosted: Mon Sep 20, 2010 8:45 pm    Post subject: "SELECT 1 FROM TABLE" - What this query do?
Reply with quote

Hi

I would like to know the use of syntax , SELECT 1 FROM TABLE. What does this query do ? I don't have access to mainframes or spufi to check it out. Could anybody help me out with this.I did try searching in the forum , but could not come across anything related to this.

Thanks
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Sep 20, 2010 9:28 pm    Post subject:
Reply with quote

Hello,

Suggest you post the complete query. . .

It is possibly used for existence checking. . .
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Tue Sep 21, 2010 1:52 am    Post subject:
Reply with quote

SELECT 1 is used when you want to check JUST the existence or not, I mean, you want to retrieve nothing from the table, so db2 does not need to access any (extra) data nor index in order to retrieve it.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Tue Sep 21, 2010 5:07 am    Post subject:
Reply with quote

acevedo wrote:
SELECT 1 is used when you want to check JUST the existence or not, I mean, you want to retrieve nothing from the table, so db2 does not need to access any (extra) data nor index in order to retrieve it.


acevedo,
index access may or may not be required depending upon the WHERE clause.

Thanks,
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 21, 2010 5:19 am    Post subject:
Reply with quote

Hello,

It would help if Ambili posts the complete query. . .

As posted, i can only hope that if this is the entire query, it is not run on a table with several million rows. That would be a lot of ones. . .
Back to top
View user's profile Send private message
Ambili S

Active User


Joined: 06 Sep 2007
Posts: 112
Location: India

PostPosted: Tue Sep 21, 2010 8:06 am    Post subject: Reply to: SELECT 1 FROM TABLE
Reply with quote

Hi Dick

Here's the SQL.

Code:
SELECT * FROM DSN8910.EMP X
   WHERE  JOB = 'DESIGNER'
      AND  EXISTS (SELECT 1
                  FROM   DSN8910.PROJ
                  WHERE  DEPTNO = X.WORKDEPT
                     AND  MAJPROJ = 'MA2100');


Could you send me some links which explains EXISTS and SELECT 1 FROM elaborately.

thanks
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 21, 2010 8:29 am    Post subject:
Reply with quote

Hello,

"SELECT 1" selects a literal just as "SELECT col_name" selects the named column.

Quote:
Could you send me some links which explains EXISTS . . .
At the top of the page is a link to "IBM Manuals". Among these are documentation for multiple releases of DB2. Select the SQL Reference for your release and search for WHERE EXISTS (the flashlight/tubelight near the top left).

In the same manual is lots of discussion about SELECT (probably much more than you want - but it is there) icon_smile.gif

If you find something n the manual that is not clear, post what you found and your doubt. Someone will be able to clarify.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Sep 21, 2010 12:17 pm    Post subject:
Reply with quote

Which columns/literals you put in a where exists() is really irrelevant(since DB2 v3 or v4). No actual columns are retrieved. Coding a 1 is a technique to easily see it is about an exists() and based on the following:

Used standalone in a program:
Code:
exec SQL
   select 1 from tab1 where ....
end-exec
if sqlcode = +100
....

1 is the shortest and fastest column-list.
because no extra columns are needed in the program, none are retrieved.
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Tue Sep 21, 2010 12:48 pm    Post subject:
Reply with quote

sqlcode1 wrote:
acevedo wrote:
SELECT 1 is used when you want to check JUST the existence or not, I mean, you want to retrieve nothing from the table, so db2 does not need to access any (extra) data nor index in order to retrieve it.


acevedo,
index access may or may not be required depending upon the WHERE clause.

Thanks,



it's not a question about the selection about the index BUT about the data to be retrieved.
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Tue Sep 21, 2010 12:58 pm    Post subject: Reply to: SELECT 1 FROM TABLE
Reply with quote

This is just what I have in my 'notebook' about SELECT ONE:

if you just need to check if there are rows that satisfy the query...SELECT ONE it's the cheapest way.

and I add FETCH FIRST 1 ROW ONLY in case it could exist more than one.

just my 2 cents.

;)
Back to top
View user's profile Send private message
Ambili S

Active User


Joined: 06 Sep 2007
Posts: 112
Location: India

PostPosted: Tue Sep 21, 2010 1:36 pm    Post subject:
Reply with quote

Thanks everyone for the solution icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm
No new posts hot to get details when "EXEC CI... Andi1982 CICS 11 Tue Sep 20, 2016 5:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us