Solved

Help in Understanding a SQL Query

Posted on 2016-11-11
7
26 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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 250 total points
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
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
Comment Utility
Thanks Vitor and Pawan. it explains it all. Learning very quickly with the help of you guys.

thanks again
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 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

15 Experts available now in Live!

Get 1:1 Help Now