[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help in Understanding a SQL Query

Posted on 2016-11-11
7
Medium Priority
?
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 52

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 32

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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 32

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 52

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 52

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

656 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