InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 16:52:55 GMT20http://www.sqlservercentral.com/Forums/Topic476756-338-1.aspx[quote][b]Jim Russell (4/11/2008)[/b][hr]Understand, but are you suggesting that "," as a join operator is now discouraged?Thanks for taking your time to contribute![/quote]Actually - it has for some time, since it can lead to ambiguous statements. There is a slow but steady push towards using ANSI notation for joins, since it disambiguates what should be used as part of defining the relation/join, and what should be used to limit rows.Fri, 11 Apr 2008 08:33:05 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic476756-338-1.aspx[quote]"Dunno about you though, but I very seldom have a query that it a true cartesian product."[/quote]I have many (well at least some) that are important.Consider a table of employees e with start dates and end dates. If you need to calculate headcounts on the first of every month for the past 7 years, you build a table d of 84 dates (1/1/2002 ... 12/1/2008), cross join (I'd use a comma) the two tables, in the where clause, specify d.date between e.hireDate and e.lastDate, and use groupby and sum(*) to get the counts.I would have changed your comments to:[code]SELECT Column1, col2, col3 from Table1, table2 -- cross join (cartisian product) of tablesWHERE Table1.col1 = Table2.col2 -- select matches of interest[/code][quote]Make any sense?[/quote]Understand, but are you suggesting that "," as a join operator is now discouraged?Thanks for taking your time to contribute!Fri, 11 Apr 2008 08:06:35 GMTJim Russell-390299http://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxI wasn't speaking about the occations when yuo need a cross join. If you need a full cartesian product, then just list the tables. Dunno about you though, but I very seldom have a query that it a true cartesian product.By join, I'm refering to the Limiting of a potential cartesian product by specifying the linking columns.What I meant is that this style is not recommended any more. Among other reasons, it can get difficult to read and it's all to easy to miss a join.[code]SELECT Column1, col2, col3 from Table1, table2 -- from clause has no joins. It's a straight list of tablesWHERE Table1.col1 = Table2.col2 -- join is done in the where clause[/code]More encouraged is the following.[code]SELECT Column1, col2, col3 from Table1 INNER JOIN Table2 ON Table1.col1 = Table2.col2 -- join is explicit on what tables and is in the from clause[/code]Make any sense?Fri, 11 Apr 2008 07:21:34 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxI have learned that if Gail says it, it must be true. So when I trip over something she says, I worry that I am misunderstanding something. She said:"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."The original query used the form FROM tableA,tableB, which yields a cartesian product which gets pruned by the subsequent WHERE clause. (No debate on depreciating the old style outer join notation, and in the case under discussion, as you suggested, an INNER JOIN is clearer.)But I think the phrase "joins in the where clause" is misleading, the join is being done in the FROM clause.I find cartesian product joins extremely powerful and useful in many situations, and I don't understand why I should replace one keystroke (",") with 12 (" CROSS JOIN ".) Is the "," construct now being discouraged?Fri, 11 Apr 2008 06:07:33 GMTJim Russell-390299http://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxhowever much I'd like to take credit for that article, it's not mine. :) Jeff Smith wrote it - his blog is very good btw.Thu, 10 Apr 2008 14:50:52 GMTjezeminehttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHI jezemine,Thank you for the article, it is much closer to the idea that I've been having about "breaking it down" piece by piece.At this moment, I am going to submit another post titled, "Interpreting the Query."If you have a moment, please take a look and respond.In the meantime, I will look over your article more closely, and I'd like to ask you a couple of questions about it.Talk with you soon....;)-Roberta-Thu, 10 Apr 2008 13:47:16 GMTrobertafrickerhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxI think this is a nice little article illustrating a good, systematic method for attacking complicated queries:[url=http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx]http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx[/url]Thu, 10 Apr 2008 00:15:49 GMTjezeminehttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHello GS,I apologize for the late response, I have been piled up big time...:exclamationmark:I will look over your info tomorrow and respond.Thank you again for your continued input...-Roberta-Wed, 09 Apr 2008 19:50:54 GMTrobertafrickerhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHI,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-Wed, 09 Apr 2008 19:46:45 GMTrobertafrickerhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxIt 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.Col2Just in this case it's a join between a table and a subqueryTue, 01 Apr 2008 09:28:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHi 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 pubsSELECT p.pub_id, p.pub_nameFROM publishers AS p2. Derived tables are [b]result sets [u][/u][/b]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.Col1Thanks, u rock...:cool:-RF-Mon, 31 Mar 2008 22:56:16 GMTrobertafrickerhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHello 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 readyfor prime time [i]yet[/i].Pls see my other response, gracias.-RF-Mon, 31 Mar 2008 22:10:40 GMTrobertafrickerhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHey 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 [b]interpret [i][/i][/b]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-Mon, 31 Mar 2008 22:08:44 GMTrobertafrickerhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHi 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-Mon, 31 Mar 2008 22:03:06 GMTrobertafrickerhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHi 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-Mon, 31 Mar 2008 21:59:22 GMTrobertafrickerhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxRoberta: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:[code]select (select title from dbo.Titles where gender = People.gender) as Title,FirstName, LastNamefrom dbo.People[/code]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?Mon, 31 Mar 2008 11:26:46 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxOn 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...Mon, 31 Mar 2008 06:50:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspx[quote][b]rbarryyoung (3/30/2008)[/b][hr]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: [code](Select col1 From ... )[/code] 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:[code]WHERE value IN(Select col1 From ... )[/code] 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:[code]WHERE EXISTS(Select * From ... )[/code] 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.[/quote]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.[code]SELECT TheTable.Col1, TheDerivedTable.Col2, TheDerivedTable.SumOfCol2, TheTable.Col4FROM TheTable INNER JOIN (SELECT Col1, Col2, SUM(Col3) AS SumOfCol3 FROM SomeOtherTable GROUP BY Col1, Col2) TheDerivedTable ON TheTable.Col1 = TheDerivedTable.Col1[/code]Mon, 31 Mar 2008 06:23:10 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspx[quote][b]robertafricker (3/30/2008)[/b][hr]Hi NJ,(this is the second one I had)USE BeerdrinkersSELECT barFROM serves, likesWHERE serves.beer = likes.beerAND drinker = ‘Joe’[/quote]If I may make a minor correction...SELECT barFROM serves INNER JOIN likes ON serves.beer = likes.beerAND 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.Mon, 31 Mar 2008 06:16:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHi 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 BeerdrinkersSELECT barFROM servesWHERE beer IN (SELECT beer FROM likes WHERE drinker = ‘Joe’)(this is the second one I had)USE BeerdrinkersSELECT barFROM serves, likesWHERE serves.beer = likes.beerAND drinker = ‘Joe’Thanks for the feedback. I have many more queries I'll be submitting over the next several days.-Roberta-Sun, 30 Mar 2008 23:08:44 GMTrobertafrickerhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxIt 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: [code](Select col1 From ... )[/code] 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:[code]WHERE value IN(Select col1 From ... )[/code] 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:[code]WHERE EXISTS(Select * From ... )[/code] 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:[code]USE BeerdrinkersSELECT barFROM servesWHERE beer(SELECT FROM likes WHERE drinker = ‘Joe’)[/code] 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).Sun, 30 Mar 2008 21:52:04 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxYour 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=74Sun, 30 Mar 2008 21:06:26 GMTN Jhttp://www.sqlservercentral.com/Forums/Topic476756-338-1.aspxHello 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 DrinkersTables and columns: Frequents: drinker, bar Serves: bar, beer Likes: drinker, barQuery: List the bars that serve a beer that 'Joe' likes.USE BeerdrinkersSELECT barFROM servesWHERE beer(SELECT FROM likes WHERE drinker = ‘Joe’)Let me know, thanks!Sun, 30 Mar 2008 19:50:03 GMTrobertafricker