|
View previous topic :: View next topic
|
| Author |
Message |
Ambili S
Active User
Joined: 06 Sep 2007
Posts: 112
Location: India
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you post the complete query. . .
It is possibly used for existence checking. . . |
|
| Back to top |
|
|
acevedo
Active User
Joined: 11 May 2005
Posts: 344
Location: Spain
|
|
|
|
| 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 |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Ambili S
Active User
Joined: 06 Sep 2007
Posts: 112
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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) 
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005
Posts: 344
Location: Spain
|
|
|
|
| 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 |
|
|
acevedo
Active User
Joined: 11 May 2005
Posts: 344
Location: Spain
|
|
|
|
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 |
|
|
Ambili S
Active User
Joined: 06 Sep 2007
Posts: 112
Location: India
|
|
|
|
| Thanks everyone for the solution |
|
| Back to top |
|
|
|
|
