joins

VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY used Ask the Experts™
on
what is the use of inner joins,i know very well inner joins are used to join two or more tables.
But we can right a simple query without joins as well.So we need this function. is there any other benefits like sql engine performs?In this case we need even to get bother both primary key,foreign key and candidate key?Please assist on my doubt.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

maybe a quote from the book "Inside Microsoft SQL Server 2005 - T-SQL Querying" (a must-read book!) helps you with this question. The author(s) knows very much about the inner processes of SQL Server so I would say there is no doubt about the following:

Page 270:
"Inner Joins

Inner joins are used to match rows between two tables bases on some criterion. Out of the first three query logical processing phases, inner joins apply the first two - namely, Cartesian product and ON filter. There's no phase that adds outer rows. Consequently, if an INNER JOIN query contains both an ON clause and a WHERE clause, logically they are applied one after the other. With one exception, there's no difference between specifying a logical expression in the ON clause or in the WHERE clause of an INNER JOIN, because there's no intermediate step that add outer rows between the two.

The one exception is when you specify GROUP BY ALL. Remember that GROUP BY ALL adds back groups that where filtered out by the WHERE clause, but it does not add back groups that were filtered out by the ON clause. Remember also that this is a nonstandard legacy feature that you should avoid using.

As for performance, when not using the GROUP BY ALL option, you will typically get the same plan regardless of where you place the filter expression. That's because the optiomizer is aware that there's no difference. I should always be cautious when saying such things related to optimization choices vecause the process is so dynamic.

As for the two supported join syntaxes, using the ANSI SQL:1992 syntax, you have more flexibility in choosing which clause you will use to specify a filter expression. Because logically it makes no difference where you place your filters, and typically there's also no performance difference, your guideline should be natural and intuitive writing. Write in a way that feels more natural to you and the programmers who need maintain your code.
[...]"

I think there's nothing to add to these statements. Remember that this only affects INNER JOINs, not the other types.

Cheers,

Christian

Commented:
Please have a look in the following link... It answers to your question's I think

http://www.techrepublic.com/article/sql-basics-query-multiple-tables/1050307
I think the question is moer eabout the syntax of the JOIN operation. In the old times it used to be:

SELECT * from tbl_a A, tbl_b B WHERE A.col_a = B.col_b AND...

while with ANSI 92 the standars changed to

SELECT * from tbl_a A INNER JOIN tbl_b B ON A.col_a = B.col_b WHERE ...

A join is really a Cartesian product of the rows in the 2 tables, that means that all the rows in one table will be paired with all the rows in the second table. However these kind of matching rarely is needed so normally we would like to have a criteria, or filter, on which the match should be done. Usually this is done by having a filter condition that match the 2 tables on one or more columns.

In the relational design we have a parent table and a child table and there is a relation between them based on a column. Parent table has a primary key(PK), which is also present in the child table but is called a foreign key(FK). A FK value in the child table will point to a PK in the parent table and so the relationship is defined and it will also be used as a filter in the join operation.

I would say the the second syntax is clearer in terms of what we want to do and also is more flexible. Very often beside the join itself we also want to do more filtering based on other criteria. With the first syntax all those conditions will have to be in the WHERE clause as with the second syntax is easy to separate them between ON and WHERE clause. Another important issue is with OUTER JOINS(*= and =*) where the first syntax becomes fairly complicated and in SQL server even produces unexpected results.

The rule would be to use in the ON clause ONLY the condition that defines the relationship based on PKs and FKs and to use in the WHERE clause the filtering of the rows based on different columns.

In terms of performance there is no difference between the 2 syntax but as I said the second offers a clearer picture of what is needed and also gives more flexibility. Keep in mind that the SQL language was built to be as close to human language as possible and if that the case I would say that even in English language the second syntax makes more sense than the first one and is easier to understand even by a someone that is not a database developer.

Hope this helps.
To add something about the above: I personally would always prefer the ON clause as it is the standard which any programmer understands directly and has a clear syntax.

The OUTER JOIN syntax using "*=" and "=*" is deprecated and should not be used anymore, in SQL Server only supported by using backward compatibility (this syntax was never ANSI standard and only supported by SQL Server). On the other hand the older syntax using WHERE instead of ON is NOT deprecated and can normally be used furthermore.

Cheers,

Christian
INNER JOIN completes the JOIN classification:

The JOIN is either LEFT JOIN, or RIGHT JOIN or OUTER JOIN or <what>? - INNER JOIN.

Without INNER JOIN how you can tell that the JOIN is not LEFT JOIN and not RIGHT JOIN and not OUTER JOIN?

How?

The JOIN is JOIN. - Wrong.
The JOIN is INNER JOIN. - Right.
Ephraim WangoyaSoftware Engineer

Commented:

I suggest you take a look at this site

http://www.w3schools.com/sql/sql_join.asp
fomand:

you can write a query with stating with joins. Join is treated as inner joins by default
Ephraim WangoyaSoftware Engineer

Commented:
Yes, if you dont specify what type of join then by default its an inner join
I know that JOIN is by default INNER JOIN.

Historically there was only JOIN. The LEFT, RIGHT, OUTER were added later.
JOIN was left as INNER JOIN for compatibility. In spoken language INNER JOIN is more appropriate o avoid "What type of JOIN?".

What types of DB JOIN do you know? JOIN, ... haha.

ORACLE used notation

SELECT * FROM t1,t2
WHERE t1.a(+) = t2.b

before

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b

appeared.

By the way, ORACLE stuff was 60% of ISO committee at that time.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
fomand, your example is reversed. The (+) is "where the NULLS might appear", so in fact it is a RIGHT JOIN ;-).
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
INNER JOIN is one SQL Server implementation of the SEMIJOIN relational operator.  Others are IN, EXISTS and INTERSECT
Olaf DoschkeSoftware Developer

Commented:
VIVEKANANDHAN_PERIASAMY, in what way could you write an outer (left/right) join without a JOIN?

If you only talk about inner joins, you can actually put the join conditions in the where clause without fearing perfromance disadvantages or anything like that, but I'd say it's easier to read a query with join conditions put to the join of tables instead of put into the where clause.

There is no way to write left/right  joins in the form of where clauses, so there's no point in avoiding joins in general and thus no point in avoiding inner joins. Atually sometimes you might have more conditions related to the join, than just primary and foreign key matching, and the query better documents that, if you put these additional conditions into the join condition as well and not in the general filter conditions on all data involved.

Bye, Olaf.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
<<, so there's no point in avoiding joins in general and thus no point in avoiding inner joins. >>
Actually, INNER JOIN are to be avoided when there are duplicates on the joining columns since the operator returns a cartesian product of the duplicates on both tables joined.
One note about conditions in the ON clause and WHERE clause.

Be aware that the results are affected when the same conditions is added in ON clause as opposed to WHERE clause in the non INNER JOINS.

Let's say you have a LEFT JOIN between table A and table B based on their relation on PK and FK. Beside that you will have a condition on one of the columns, say col_B, fo B. If that condition will be placed in the ON clause it will affect only the B table and less rows will be matched in B but all the rows in table A will still be returned. If placed in the WHERE clause then it will affect both A and B table because where there are no matched in B for rows in A the col_B value is null and those rows will be filtered out from A as well because NULL compared to any value, even NULL, is still FALSE.


Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
<<well because NULL compared to any value, even NULL, is still FALSE.>>
Correct.  INNER JOIN is very sensitive to NULL values and duplicates on joining keys.  
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
@Olaf_Doschke,
"There is no way to write left/right  joins in the form of where clauses,"
Didn't we prove that already wrong for both MSSQL and Oracle in prior posts?
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
<<Didn't we prove that already wrong for both MSSQL and Oracle in prior posts?>>
I concur with that.  
to answer to my qusetion is

we can write right/left joins without joins function also but not in MSSQL2005 and in MSSQL2008.Thats the reason we using joins function which is inbuilt function from sql 2005 and above.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Ansi Joins work with MSSQL 2000, too, so that statement is not fully true.
NA

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial