?
Solved

Help in Understanding a SQL Query

Posted on 2016-11-11
7
Medium Priority
?
72 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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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 38

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
LVL 38

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 53

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 53

Accepted Solution

by:
Vitor Montalvão earned 1000 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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

588 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