March 30, 2008 at 7:50 pm
Hello All,
The topic of "How to Build Queries" is at the top of my list.
When it comes to deciphering how to break down the building blocks of the query into syntax parameters, I am still "not there yet." ESPECIALLY IN REGARD TO SUBQUERIES AND WHICH COMMANDS TO USE.
The following is a query example that I am currently working on.
I will post my answer underneath it, which is perhaps correct.
Please feel free to correct it and advise as to how I can better understand how the query translates into syntax. Here it is:
DB = Beer Drinkers
Tables and columns: Frequents: drinker, bar
Serves: bar, beer
Likes: drinker, bar
Query: List the bars that serve a beer that 'Joe' likes.
USE Beerdrinkers
SELECT bar
FROM serves
WHERE beer(SELECT FROM likes
WHERE drinker = ‘Joe’)
Let me know, thanks!
March 30, 2008 at 9:06 pm
Your query will not work in sql server..also you dont need subquery for this..
try this..
select bar
from serves join likes on
serves.bar = likes.bar
and likes.drinker = 'joe'
some basic information about subqueries is here
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=74
March 30, 2008 at 9:52 pm
It is important to realize that there are several types of Subquery:
1. Value Subqueries: These just return a single value per row and they look like this: (Select col1 From ... ) If they return no value, you get a NULL, if they return multiple values, you will fail with an error. They are most often used to assign a value to an output column, or to compare to a value in your where clause.
2. IN subqueries: These return any number of rows, each with a single column and they look like this:WHERE value IN(Select col1 From ... ) They are typically used in WHERE clauses to test if a particular value is in the list of values returned.
3. EXISTS subqueries: These return any number of rows with any number of columns and they look like this:WHERE EXISTS(Select * From ... ) They are typically used in WHERE clauses to test if corresponding rows in another table exist. Because the column values returned are not actually used, typicaly you use a "*" instead of bothering to name any columns.
The query that you listed:USE Beerdrinkers
SELECT bar
FROM serves
WHERE beer(SELECT FROM likes WHERE drinker = ‘Joe’)
has three obvious problems. First, "beer" is being used as a function, but "beer" is not a function: it is a column in your "serves" table and so should be used as a value in your query: either assigned to an output column, used in a formula/calculation or compared to another value.
Secondly, What kind of Subquery is this? If it is a Value subquery, then you need to use it like a value. If it is an IN subquery, you should have an "IN" and be comparing a value to the list of values that it returns. and, if it is an EXISTS subquery, then you should have an "EXISTS" and it should be alone in a WHERE or AND subclause.
Thirdly, your subquery: [font="Courier New"]SELECT FROM...[/font] has no output column. What column you pick depends on the type of subquery (or "*" for an EXISTS).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2008 at 11:08 pm
Hi NJ,
Believe it or not, I had that query as well as a 2nd option, and wasn't sure if it was correct.
In the meantime, I know now the complex query s/b:
USE Beerdrinkers
SELECT bar
FROM serves
WHERE beer IN
(SELECT beer FROM likes WHERE drinker = ‘Joe’)
(this is the second one I had)
USE Beerdrinkers
SELECT bar
FROM serves, likes
WHERE serves.beer = likes.beer
AND drinker = ‘Joe’
Thanks for the feedback. I have many more queries I'll be submitting over the next several days.
-Roberta-
March 31, 2008 at 6:16 am
robertafricker (3/30/2008)
Hi NJ,(this is the second one I had)
USE Beerdrinkers
SELECT bar
FROM serves, likes
WHERE serves.beer = likes.beer
AND drinker = ‘Joe’
If I may make a minor correction...
SELECT bar
FROM serves INNER JOIN likes ON serves.beer = likes.beer
AND drinker = 'Joe'
It's not recommended to do joins in the where clause any more. Especially since, in 2005 and higher, the old style outer join (*=) does not work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2008 at 6:23 am
rbarryyoung (3/30/2008)
It is important to realize that there are several types of Subquery:1. Value Subqueries: These just return a single value per row and they look like this:
(Select col1 From ... )If they return no value, you get a NULL, if they return multiple values, you will fail with an error. They are most often used to assign a value to an output column, or to compare to a value in your where clause.2. IN subqueries: These return any number of rows, each with a single column and they look like this:
WHERE value IN(Select col1 From ... )They are typically used in WHERE clauses to test if a particular value is in the list of values returned.3. EXISTS subqueries: These return any number of rows with any number of columns and they look like this:
WHERE EXISTS(Select * From ... )They are typically used in WHERE clauses to test if corresponding rows in another table exist. Because the column values returned are not actually used, typicaly you use a "*" instead of bothering to name any columns.
4. Derived tables: These are used in the From clause, may return any number of columns and any number of rows. Once aliased, they can be joined to other tables and treated just like a table in the other clauses of the query.
Eg.
SELECT TheTable.Col1, TheDerivedTable.Col2, TheDerivedTable.SumOfCol2, TheTable.Col4
FROM TheTable
INNER JOIN
(SELECT Col1, Col2, SUM(Col3) AS SumOfCol3
FROM SomeOtherTable
GROUP BY Col1, Col2) TheDerivedTable
ON TheTable.Col1 = TheDerivedTable.Col1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2008 at 6:50 am
On the subject of "Derived Tables", look into CTE's... they make for easier to read code and can be self aliased, if need be, without duplicating the "sub-query".
Also, take a look at the article at the URL in my signature line...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2008 at 11:26 am
Roberta:
Sub-queries are exactly what their name says, they are a query inside another query.
In the case you outlined, a join is better than a sub-query. They will both work, but the join will usually be faster. (Hidden little secret, SQL will often convert the "in (subquery)" code to a join behind the scenes, for exactly this reason. It just won't tell you it's doing it. Of course, if you look at the query plan, then the cat's out of the bag and you'll see the join.)
The way to write sub-queries is the same way you write any other query. They have the same rules. The only exception is that inline sub-queries can only return one row.
Inline sub-queries are where you have a sub-query in your Select clause, instead of your From or Where clause, or where the sub-query follows "=" instead of "in". They can also be used in Order By, and Group By clauses, the same as Select clauses.
For example:
select
(select title
from dbo.Titles
where gender = People.gender) as Title,
FirstName, LastName
from dbo.People
This assumes you have a "gender" column in your People table, and another gender column in your Titles table, and the two can match.
Again, this would be better solved with a join, but it's just a simple example to demonstrate the concept of an inline sub-query.
But you'll see the main rule, that sub-queries have to work the same way as main-queries. It has Select, it has From, it has Where. Same rules, same syntax, just the added rule that it can only return one row.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2008 at 9:59 pm
Hi NJ,
Thanks for the site, I hadn't noticed it before. I checked it out, and it looks like a good resource.
I am going to submit a different type of query question later tonight, so please keep an eye out...;)
=Roberta-
March 31, 2008 at 10:03 pm
Hi rbarryyoung,
Thank you for breaking down the "IN" and "EXISTS" definitions.
The thing is, I am just not quite there yet as far as knowing in advance what I would like the result set to look like.
Where I am is in trying to figure out how to interpret a query piece by piece and then turn it into syntax.
So, what I'm going to do is submit another post titled, "Interpreting the Query" and ask some specific questions about "building" the syntax.
Once I understand this more clearly, I will understand result sets more clearly. Also, SEEING a result set is always so much more helpful as Gail sent me for WITH CUBE and WITH ROLLUP.
Your explanations are simple and clear, so pls keep an eye out for the next post, "Interpreting the Query." Thanks!
-Roberta-
March 31, 2008 at 10:08 pm
Hey Gail,
Thanks for the "inside dope." I find what you said to be very interesting.
I have built queries using the ON portion of the syntax, although not often as of yet.
I will keep this info in my bag of tricks.
Also, I realized that since right now I am trying to figure out how to interpret a query piece by piece and then turn it into syntax, I need to submit a different type of post.
It's going to be called "Interpreting the Query" and ask some specific questions about "building" the syntax.
Your WITH CUBE and WITH ROLLUP link was especially helpful as it showed the result set tables and really helped me to "see" the thang.
Pls keep an eye out for the next post, "Interpreting the Query." Thanks!
-Roberta-
March 31, 2008 at 10:10 pm
Hello Gail,
I have saved your info for a later date when I'm ready for it.
I am familiar with aliases and derived tables, however, not ready
for prime time yet.
Pls see my other response, gracias.
-RF-
March 31, 2008 at 10:56 pm
Hi again,
Jeff's post about CTE's and derived tables got my looking at your post again even thought I'm sending a new post on building queries.
I'd like to tell you what I think you are saying so that I can understand the syntax more clearly. Here goes:
1. Major "Aha" for me on the definition of "alias." I have been very aware of that usage in syntax however did not know it was referred to as alias, e.g.
USE pubs
SELECT p.pub_id, p.pub_name
FROM publishers AS p
2. Derived tables are result sets used as table sources in a query.
And then there's the syntax, nicely built by the way, I understand most of it except, please remind me what this line means:
ON TheTable.Col1 = TheDerivedTable.Col1
Thanks, u rock...:cool:
-RF-
April 1, 2008 at 9:28 am
It specified on what columns the join is done.
Same as for a normal inner/outer join between two tables:
FROM Itable1 INNER JOIN Table2 On Table1.Col1 = Table2.Col2
Just in this case it's a join between a table and a subquery
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2008 at 7:46 pm
HI,
Sorry for the delayed response, I got piled up. Thanks for the explanation on the "ON" portion of the syntax. I'll keep it in my
stockpile.
I should be posting another syntax question shortly, promise...:D
-RF-
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply