Solved

Help in Understanding a SQL Query

Posted on 2016-11-11
7
30 Views
Last Modified: 2016-11-11
Hi guys,

Can you please help me understand this query
SELECT  Point.STAsText() AS Wkt
	FROM AddressSearchString AS S
    WHERE NOT EXISTS (SELECT 42
					FROM [PhysicalSpace].[LocationShapes]
                    WHERE S.Point.STDistance(ShapeData) < 3.0 AND [IsPoint] = 1)
	GROUP BY Point.STAsText()

Open in new window


What does Select 42 means?

regards
0
Comment
Question by:shah36
  • 3
  • 2
  • 2
7 Comments
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41883801
What does Select 42 means?
That's a constant. It could be anything since what's being returned by that SELECT doesn't have any effect on the main SELECT (you aren't comparing columns there).
Typically you'll see there a NULL, * or 1 but 42 is somehow odd but has the same purpose.
0
 
LVL 23

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41883803
42 is just a constant , you can put any value there. Normally people put 1 there. Exists just checks whether you are returning any row. So for any row we can return any value which is a constant value.

Now if you return column values then that might slow the performance of the query and the engine has to return data from the table to the outer query.

So constant inside of a exists clause help as we are not returning  the column values, Engine work a bit less..:)

SELECT  Point.STAsText() AS Wkt
	FROM AddressSearchString AS S
    WHERE NOT EXISTS (SELECT 42
					FROM [PhysicalSpace].[LocationShapes]
                    WHERE S.Point.STDistance(ShapeData) < 3.0 AND [IsPoint] = 1)
	GROUP BY Point.STAsText()

Open in new window


Hope it helps !
0
 

Author Comment

by:shah36
ID: 41883804
Does this mean that there is no purpose of subquery here as it will always return null as 42 doesn't exist in the subquery table?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41883807
No there is purpose , It is used in the where clause. In this case we need to take results where we should NOT have any rows from the Inner query.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41883810
To be honest all query seems very odd to me. You have 2 tables and there's no apparent connection between them.
Is the following query returning the same rows as the original one?
SELECT  Point.STAsText() AS Wkt
FROM AddressSearchString AS S
WHERE S.Point.STDistance(ShapeData) < 3.0 
GROUP BY Point.STAsText()

Open in new window

0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41883811
Humm, maybe I was too fast. Those functions are Geography functions and then this might make sense so ignore my last comment, please.
What that query is doing is returning all records where the distance is greater than 3.0 (I'm not sure which metric system is being used here so can be miles or kms or even meters).
0
 

Author Closing Comment

by:shah36
ID: 41883815
Thanks Vitor and Pawan. it explains it all. Learning very quickly with the help of you guys.

thanks again
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now