May 18, 2014 at 8:00 pm
Just wondering why this SELECT TOP subquery works in the below sql query, when it doesn't have a FROM clause. What are keywords I can search to learn more about this kind of syntax?
SELECT a.PostalAddress, s.suburbName FROM CrapAddresses a
INNER JOIN suburbLkp s ON a.PostalAddress LIKE
(SELECT TOP 1 '%' + s.suburbName + '%')
I got this stuff from this site, if you're wondering http://www.codeproject.com/Articles/67518/A-good-use-for-SQL-OUTER-APPLY about context.
create table suburbLkp
(suburbName varchar(30));
insert into suburbLkp values
('Motueka'),
('Moturoa'),
('Mount Albert'),
('Mount Cook'),
('Mount Cook National Park'),
('Mount Eden'),
('Mount Grand'),
('Mount Maunganui'),
('Mount Pleasant'),
('Mount Roskill'),
('Mount Victoria'),
('Mount Wellington'),
('Murchison'),
('Muritai'),
('Murrays Bay');
create table CrapAddresses
(PostalAddress varchar(100));
insert into CrapAddresses
values
('Wellington Highway Mount Wellington'),
('2120 Mount Wellington'),
('Road Mount Albert'),
('nnell Avenue Mount Roskill'),
('ose Road Mount Wellington'),
('Street Mount Eden'),
('Avenue Mount Eden'),
('View Road Bastia Hill'),
('North Road Mount Albert'),
('Eden Road Mt Eden'),
('2287 mount Wellington');
May 18, 2014 at 10:12 pm
KoldCoffee (5/18/2014)
Just wondering why this SELECT TOP subquery works in the below sql query, when it doesn't have a FROM clause. What are keywords I can search to learn more about this kind of syntax?
SELECT a.PostalAddress, s.suburbName
FROM CrapAddresses a
INNER JOIN suburbLkp s ON a.PostalAddress LIKE
(SELECT TOP 1 '%' + s.suburbName + '%')
Well, you do have a where clause. Notice the subquery is part of the join condition and is referencing an aliased column in the outer query. The outer query has a from clause. The query that is written is not how I would write it. The following returns the same results, uses the same execution plan and pretty much tells us that the Query Optimizer translates your query to something like this.
SELECT a.PostalAddress, s.suburbName
FROM CrapAddresses a
INNER JOIN suburbLkp s
ON a.PostalAddress LIKE '%' + s.suburbName + '%'
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2014 at 10:49 pm
Even your syntax is new stuff for me. The only joins I've ever used are of two kinds: ON and WHERE a.x = b.x. Never ON-LIKE. What is this called?
The author of that blog gave the following as his personal best, illustrating OUTER APPLY
SELECT
a.CompleteAddress
s1.Suburb
FROM
tblCrap_Address a
OUTER APPLY
(SELECT TOP 1 s2.Suburb FROM tblLookup_Suburb s2 where
a.CompleteAddress
LIKE '%' + s2.Suburb + '%' ORDER BY LEN(s2.Suburb) DESC) s1
May 19, 2014 at 7:53 am
There is no special name for it. It's just another way of writing a join condition.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2014 at 8:51 am
SQLRNNR (5/19/2014)
There is no special name for it. It's just another way of writing a join condition.
Actually, it could be considered as a non equi join. You can find a lot of information on the internet or any relational databases' book.
May 19, 2014 at 8:53 am
Oh, it is a non equi join! For example.
http://www.w3resource.com/sql/joins/perform-a-non-equi-join.php
Thanks.
May 19, 2014 at 9:11 am
Luis Cazares (5/19/2014)
SQLRNNR (5/19/2014)
There is no special name for it. It's just another way of writing a join condition.Actually, it could be considered as a non equi join. You can find a lot of information on the internet or any relational databases' book.
You could go the description route. I don't do that. I tend to look at the operators involved in the join which gives us logical and physical operators. In this case you get a physical of nested loops (based on the amount of data involved) with an Inner Join logical type.
But yes, technically due to the lack of the equality operator, this would be a non-equi class of an inner join.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2014 at 9:48 am
I'm sorry if I didn't make myself clear. The reference was supposed to be for KoldCoffee, as I assumed that you knew this. 😉
May 19, 2014 at 9:57 am
ja ja, SQLRNNR, I get you, but I need to know the components available before making any decisions. Probably the equi join is good for something in some other cases, so wanna know what the heck that syntax is called so can read up on it.
Thanks for help.
May 19, 2014 at 9:58 am
Luis Cazares (5/19/2014)
I'm sorry if I didn't make myself clear. The reference was supposed to be for KoldCoffee, as I assumed that you knew this. 😉
:hehe::cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2014 at 9:59 am
KoldCoffee (5/19/2014)
ja ja, SQLRNNR, I get you, but I need to know the components available before making any decisions. Probably the equi join is good for something in some other cases, so wanna know what the heck that syntax is called so can read up on it.Thanks for help.
Good luck in your research
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2014 at 10:04 am
hmmm, is equi join just no good? Can I just ignore that syntax totally as functional residue, little use, less time wasted learning it the better? Cause I'd like to scratch it off my list of 'to do'.
May 19, 2014 at 10:10 am
The use cases for a join using "LIKE" in the join conditions will be far fewer than your traditional <, >, <>, = operators. There is a benefit to learning them, but I would probably worry about understanding the basics first (the more standard operators).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply