JOINS and WHERE clause

hello,
I was curious and wanted to know if there was a difference between the
following statements.
SELECT FirstName, LastName
FROM Person
INNER JOIN Address on Person.PersonID = Address.PersonID AND
Address.AddressType = 'Home'
and:
SELECT FirstName, LastName
FROM Person
INNER JOIN Address on Person.PersonID = Address.PersonID
WHERE
Address.AddressType = 'Home'
thank you for your help
BenDid you compare the execution plans? Did you trace the execution of each
query in SQL Profiler? (Use dbcc freeproccache between each take.)
ML
http://milambda.blogspot.com/|||You will get the same results, as for the performance:
Is Person a table or a view? If a view, how complex is it?|||The answer is yes, and no. Yes, these two queries are mathematically
equivalent. However, they are asking two different questions. Not as bad
as what color is the sky and what color is a Marge Simpson's hair, but
different questions.
The FROM clause builds a working set of data based on the JOINS. The WHERE
clause critieria is applied to each row from that set individually and rows
removed accordingly.
In your two queries:
* The first is building a set of rows by joining person and address,
looking for rows where the personIds match and address.addressType is home.
* The second is building a set of rows by joining person and address,
looking for rows where the personIds match. Then it is going back over this
set and tossing out any where Address.AddressType is not home.
Why this is significant is not so much for queries like this, in completely
INNER JOIN queries the results will always be the same. (someone correct me
if there is a case where it can be different) However, when OUTER JOINs are
involved, things get much more interesting. Say this was and OUTER JOIN:
SELECT FirstName, LastName
FROM Person
LEFT OUTER JOIN Address on Person.PersonID = Address.PersonID
WHERE Address.AddressType = 'Home'
So when the set is assembled, all rows in the Person table will be included
in the output, and only address rows where there is a match.
Well, when the WHERE clause is applied to the results of the FROM clause,
person rows that did not have addresses would fall out of the set, since the
addressType of the null row is null, not home.
So the point is that you need to query the table like you mean it, if you
are shaping the set of data with your criteria, it goes in the FROM clause,
if it is eliminating rows from the set, put it in the WHERE clause.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<dreamer@.gmail.com> wrote in message
news:1138136404.172038.122140@.g47g2000cwa.googlegroups.com...
> hello,
> I was curious and wanted to know if there was a difference between the
> following statements.
> SELECT FirstName, LastName
> FROM Person
> INNER JOIN Address on Person.PersonID = Address.PersonID AND
> Address.AddressType = 'Home'
> and:
> SELECT FirstName, LastName
> FROM Person
> INNER JOIN Address on Person.PersonID = Address.PersonID
> WHERE
> Address.AddressType = 'Home'
> thank you for your help
> Ben
>

joins

Hi Friends
I need ur advise on this one.
when writing sql statements and whenever there is need to join 2 or more tables am used to the following syntax (using WHERE to join tables)

select a.name,b.code from table a,table b where a.code=b.code

as you all know we can also use INNER JOINS,OUTER JOINS etc., to join tables and all BOL help example contains this type of syntax.

my question is , am having difficulty using/writing this syntax in my queries .meaning uysing left joins,inner joins in the same queries.I appreciate if u guys can direct me to some good articles or that sort of thing to improve my understanding of this type of syntax.
Thanks

Hi,

IMHO, the information you need is in BOL. As you have mentioned, it contains samples perjorming JOIN querries. All you have to do is experiment with it to get the idea behind it. Use Query Analyzer to achieve this.

cheers,

Paul June A. Domag

|||

See if these help:

--inner join
select a.name,b.code
from table a inner join table b on a.code=b.code

--left join
select a.name,b.code
from table a left join table b on a.code=b.code

--right join
select a.name,b.code
from table a right join table b on a.code=b.code

--full join
select a.name,b.code
from table a full join table b on a.code=b.code

--cross join
select a.name,b.code
from table a cross join table b

|||See if this helps:
http://www.firstsql.com/tutor.htm and especially this http://www.firstsql.com/tutor3.htm#join
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)|||Thanks Frank.That helps mate |||

Hi Paul

Seems like you are very helpful.........

Please assist.

i have several document groups in a table which data i want to export. my statement is: select * from docs_indexed where di_doc_group = 71. this is working and i get the correct results. problem is, i have 350 doc_groups. how do i create a query where i can get results for all the doc_groups in one query?

Regards

Hannes

joins

Hi Friends
I need ur advise on this one.
when writing sql statements and whenever there is need to join 2 or more tables am used to the following syntax (using WHERE to join tables)

select a.name,b.code from table a,table b where a.code=b.code

as you all know we can also use INNER JOINS,OUTER JOINS etc., to join tables and all BOL help example contains this type of syntax.

my question is , am having difficulty using/writing this syntax in my queries .meaning uysing left joins,inner joins in the same queries.I appreciate if u guys can direct me to some good articles or that sort of thing to improve my understanding of this type of syntax.
Thanks

Hi,

IMHO, the information you need is in BOL. As you have mentioned, it contains samples perjorming JOIN querries. All you have to do is experiment with it to get the idea behind it. Use Query Analyzer to achieve this.

cheers,

Paul June A. Domag

|||

See if these help:

--inner join
select a.name,b.code
from table a inner join table b on a.code=b.code

--left join
select a.name,b.code
from table a left join table b on a.code=b.code

--right join
select a.name,b.code
from table a right join table b on a.code=b.code

--full join
select a.name,b.code
from table a full join table b on a.code=b.code

--cross join
select a.name,b.code
from table a cross join table b

|||See if this helps:
http://www.firstsql.com/tutor.htm and especially this http://www.firstsql.com/tutor3.htm#join
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||Thanks Frank.That helps mate |||

Hi Paul

Seems like you are very helpful.........

Please assist.

i have several document groups in a table which data i want to export. my statement is: select * from docs_indexed where di_doc_group = 71. this is working and i get the correct results. problem is, i have 350 doc_groups. how do i create a query where i can get results for all the doc_groups in one query?

Regards

Hannes

Join statement

Hi.......

Its been awhile since I've touched SQL statements, so I need some help
with writing a JOIN statement to query 3 tables.

The dB has 3 tables with values
Applications
-Application_code(Primary key)
-Application_name

Applications_Installed
-Computer_name(Pri key)
-Application_code(Foreign key/sec key)

Workstation_info
-Computer_name(Pri key)
-Serial_number

What I want to do is query the tables for a particular Application code
and name from Applications, so that it returns the values of computer
names with the matching values from Applications_installed and
Workstation_info.

So I need to do a

Select * from applications where applications.application_code='XXX'
join (this is the part I'm stuck how do I tell it to match the
applications.application_code =
applications_installed.application_code) then match the computer names
from Applications_installed.computer_name with that of
workstation_info.computer_name

Not sure if I'm explaining this properly...

Can anyone help......Hey Zeno,

I think what you're looking for is:

SELECT columnlist
FROM Applications a JOIN Applications_Installed ai ON
a.Application_Code = ai.Application_Code
JOIN Workstation_Info wi ON ai.Computer_Name = wi.Computer_name
WHERE a.application_code = 'XXX'

May I also suggest that you pick up a reference guid on basic SQL;
newsgroups can be harsh teachers :) Years ago, I found Ben Forta's 10
Minute Guide very good at covering the basic concepts:

http://www.amazon.com/exec/obidos/t...524660?v=glance

HTH,
Stu|||Thanks Stu.......

I'll give it a try and let you know how things go.........

Cheers|||Thanks Stu.......

I'll give it a try and let you know how things go.........

Cheers|||use ACCESS DATA PROJECTS they allow you to drag and drop fields around
instead of getting frustrating writing queries.

TSQL is too hard to just dive into -- use ADP as a crutch to help you
to start getting things done and stop worrying about syntax.|||(aaron.kempf@.gmail.com) writes:
> use ACCESS DATA PROJECTS they allow you to drag and drop fields around
> instead of getting frustrating writing queries.
> TSQL is too hard to just dive into -- use ADP as a crutch to help you
> to start getting things done and stop worrying about syntax.

I would say that this is a poor advice in the long run. Maybe if you
only compose a query once or twice a year maybe. But else learning
SQL syntax pays off.

And using Access may not necessarily be helpful, since there are
significant differences between the SQL in Access and Transact-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Join Multiple Statements for view

Hello, I have the following view for the 3rd quarter I need to include all 4 quarters in this view. The only thing that will change is the qtr name (ie qt1, qtr2 etc) the sums and the date ranges, the % of target achieved will need to be at the end and calcuate all months. How can I join each of these queries for all quarters into one big one that will output each quarter in the view, ive tried but keep getting errors? - thanks for your help

SQL> CREATE VIEW campuscont_qtr3 AS
2 SELECT campus.campus,
3 ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3",
4 (ROUND(campus.QTR3/tot_contr,2)*q3)/
5 campus.QTR3*100 "% OF TARGET ACHIEVED"
6 FROM campus,(SELECT SUM(AMOUNT) Q3
7 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
8 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q3contr,
9 (SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr;

CAMPUS QTR3 % OF TARGET ACHIEVED
-- ---- -------
Main 396.9 79.38
East 194.4 77.76
West 97.2 77.76
North 121.5 81You can make use of the UNION / UNION ALL clause

CREATE VIEW CAMPUS_CONTR_ALL_QTR
AS

SELECT
1 AS QTR,
...the rest of your select concerning the first quarter

UNION ALL

SELECT
2 AS QTR,
...the rest of your select concerning the second quarter

and so on.|||OK. looks like this will work but IM getting the wrong output. I would only like one instance of campus and all the quarters to follow. Heres what the output I receieved.

SQL> SELECT campus.campus,
2 ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3"
3 FROM campus,(SELECT SUM(AMOUNT) Q3
4 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
5 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q3contr,
6 (SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr
7 UNION ALL
8 SELECT campus.campus,
9 ROUND(campus.QTR2/tot_contr,2)*q2 "QTR2"
10 FROM campus,(SELECT SUM(AMOUNT) Q2
11 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
12 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q2contr,
13 (SELECT SUM(QTR2) Tot_Contr FROM campus) totcontr;

CAMPUS QTR3
-- ----
Main 396.9
East 194.4
West 97.2
North 121.5
Main 380.7
East 234.9
West 97.2
North 97.2|||Let's get this straight. The UNION of selects should have returned the following result :

CAMPUS QTR CONTRIBUTION
====================
Main 1 396.9
East 1 194.4
West 1 97.2
North 1 121.5
Main 2 380.7
East 2 234.9
West 2 97.2
North 2 97.2

Now what you actually would like is something like
CAMPUS QTR1 QTR2 QTR3
=====================
Main 396.9 380.7
East 194.4 234.9
West 97.2 97.2
North 121.5 97.2

Is that what you want ?

BTW, I assume that the date range in your latest select for Q2 is a typo...|||Yes, just what I wanted. How would I do it?
- thanks|||OK. To achieve this, your first query is looking good, and should be modified only slightly.

I can only suggest to use the INNER JOIN, LEFT OUTER JOIN, etc. syntax. It makes the query much easier to read.

Your final select would look like this

SELECT campus.campus,
ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3",
(ROUND(campus.QTR3/tot_contr,2)*q3)/
campus.QTR3*100 "% OF TARGET ACHIEVED"

/* CAMPUS IS YOUR DRIVING TABLE */
FROM campus

/* inner join on 1st qtr contribution, select SUM always returns 1 row */
INNER JOIN
(SELECT SUM(AMOUNT) Q1
FROM contribution wHERE CDATE >= TO_DATE('01/01/03', 'MM/DD/YY')
AND CDATE <= TO_DATE('31/03/03', 'MM/DD/YY')) Q1contr
ON 1 = 1

/* inner join on 2ND qtr contribution, select SUM always returns 1 row */
INNER JOIN
(SELECT SUM(AMOUNT) Q2
FROM contribution wHERE CDATE >= TO_DATE('01/04/03', 'MM/DD/YY')
AND CDATE <= TO_DATE('30/06/03', 'MM/DD/YY')) Q2contr
ON 1 = 1

etc..

/* inner join on total contribution */
INNER JOIN
(SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr
ON 1 = 1;


Just out of curiosity. Isn't there any relationship between "Contributions" and "Campus" (like a CAMPUS column in table "Contributions") ?