Solved

Help in Understanding a SQL Query

Posted on 2016-11-11
7
36 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 47

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 28

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 28

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 47

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 47

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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 …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 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