SQL Inner Join Question

SQL INNER JOIN question

From performance point of view, there will not be any difference... atleast not on prominent RDBMS like Sql-server / Oracle... These Database Engine, are capable of identifying both the patterns means the same and use the same execution plan for both...

In my humble opinion both are equally good practice, but you should maintain consistency and proper alignment... Somewhere I heard that where clause are generally used by oracle developers, and inner join by Sql-Server... Not very much sure about that... By now most of the coders are capable of understanding both types of queries...

Problems with INNER JOIN and LEFT/RIGHT OUTER JOIN

Semantically, joins are processed in the order they appear in the from clause. (They may not be actually executed in this order due to SQL optimizations, but the ordering is important for defining the result set.)

So, when you do:

from orders left outer join customers inner join companies

(I'm leaving out the on clauses which are a distraction for this purpose.)

The SQL is interpreted as:

from (orders left outer join customers) inner join companies

You are doing an inner join, so the values must appear on both sides. In your case, this undoes the effect of the left outer join.

You want:

from orders left outer join (customers inner join companies)

Here are some solutions.

My preferred solution is to use left outer join for all the joins. In fact, for readability and maintainability, almost every query I write is going to be only left outer join or [inner] join connecting the tables. Having to parse through the query to understand the semantics of the joins seems to be an unnecessary effort, if you can write the queries in a consistent form.

Another solution is to use parentheses:

from orders left outer join (customers inner join companies)

Another solution is a subquery:

from orders left outer join (select . . . from customers inner join companies) cc

What is the difference between INNER JOIN and OUTER JOIN?

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

SQL statement complicated INNER JOIN

So you've got the order of operators the wrong way around. The operator order of a simple query should be:

SELECT
FROM
JOIN
WHERE
ORDER

So your query would turn into:

SELECT user.id, friends.this_friend_id, friends.that_friend_id 
FROM friends 
INNER JOIN user 
ON friends.this_friend_id = user.id 
OR friends.that_friend_id = user.id;
WHERE friends.this_friend_id = 3 
OR friends.that_friend_id = 3

However using or statements is not great as it causes full scans of tables. If you wanted to create a more performant script you could do the below, where you use the union statement instead of the or to get this_friend_id and that_friend_id join.

SELECT user.id, friends.this_friend_id, friends.that_friend_id 
FROM [friends] 
INNER JOIN [user] 
ON friends.this_friend_id = user.id 
WHERE friends.this_friend_id = 3 
union
SELECT user.id, friends.this_friend_id, friends.that_friend_id 
FROM [friends] 
INNER JOIN [user] 
ON friends.that_friend_id = user.id 
WHERE friends.that_friend_id = 3

Joining 7 tables using inner joins show inaccurate results

ProductCost is per country, so when you join it on you need to also restrict it to the current country.

Note I'm also using the best practice of using table aliases which makes your query a lot easier to read.

INNER JOIN ProductCost PC ON
    PC.ProductID = P.ProductID
    AND PC.CountryId = C.CountryID

SQL inner join with conditional selection

Try this Query:

SELECT DISTINCT a.id,(CASE When b.name IS NULL OR b.name = '' Then 'No Entry' else b.name end) name FROM table_A a  
LEFT JOIN table_B b on a.id = b.id

Try This

SQL style question: INNER JOIN in FROM clause or WHERE clause?

I tend to use the FROM clause, or rather the JOIN clause itself, indenting like this (and using aliases):

SELECT t1.field1, t2.field2, t3.field3
FROM table1 t1
  INNER JOIN table2 t2
    ON t1.id1 = t2.id1
  INNER JOIN table3 t3
    ON t1.id1 = t3.id3

This keeps the join condition close to where the join is made. I find it easier to understand this way then trying to look through the WHERE clause to figure out what exactly is joined how.

SQL Inner Join Question