difference between join and no join select ?

Question

Greetings all,

I see no difference between the two queries below:

query_join = select a.id, b.name, a.telephone, a.description  from tb_industry a   left outer join tb_sector b on a.sector_id = b.id 
query_select = select a.id, b.name, a.telephone, a.description from tb_industry a , tb_sector b WHERE a.sector_id = b.id

The result is exactly the same. Now I guess this could happen, but I would like to get enlightment on what are the situations where only query_join is best, and the situations where query_condition is best?

Many thanks

Answer 1

You use joins when you want to retreive all results from the table you are selecting from and the values from the left or right table (LEFT JOIN, RIGHT JOIN) when there are matches.

When you want an explicit match you use the query condition style.

Hope it helps! w3schools has some simple and basic examples on this.

Answer 2

If they return the same results for you then every item in tb_industry has an item in tb_sector. Your second query is incorrect for the equivalent of a left join, which would be a.sector_id *= b.sector_id.

The *= syntax is deprecated and being phased out in newer RDBMS.

Answer 3

They're not the same, although they may return the same result depending on your data.

The first is a left outer join, so will return rows if the corresponding table doesn't have a matching entry.

The second is essentially a inner join, so will not return rows unless both tables have matching entries.

It depends on your preference, but the first type of syntax is easier to read when queries are complex.

Answer 4

Joins are the newer syntax to express relations in queries. They offer the benefit of outer joins, which are not really possible in a where clause (Oracle had a language extension for this, by adding a (+) to the filter, but it was very limited and not very easy to understand). When using inner joins, it doesn't matter, the result is the same.

This is subjective, but in my opinion, joins are much easier to read.

Answer 5

A Left Outer Join is not the same as a normal Join.

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).

Answer 6

your join query with left outer join will bring in even un-matched records from db. your second query will only bring in matched results

Answer 7

The first is an OUTER join. This shows rows from table A even if there is no matching row in table B. Suppose tables contain the following data:

select a.name, a.sector_id from tb_industry a;

name  sector_id
----  ---------
A     1
B     2
C     3

Select b.id, b.name from tb_sector b;

id  name
--  ----
1   X
2   Y

(Note that there is no tb_sector row with id 3.)

The outer join still returns all rows from table A, with NULLs for values that should have come from table B:

select a.name, a.sector_id, b.name as sector_name
from tb_industry a left outer join tb_sector b on a.sector_id = b.id;

name  sector_id  sector_name
----  ---------  -----------
A     1          X
B     2          Y
C     3

The other query (an INNER join) misses the unmatched row:

select a.name, a.sector_id, b.name as sector_name
from tb_industry , tb_sector b where a.sector_id = b.id;

name  sector_id  sector_name
----  ---------  -----------
A     1          X
B     2          Y

The following query is also an inner join, using the newer ANSI join syntax:

select a.name, a.sector_id, b.name as sector_name
from tb_industry a
join tb_sector b on a.sector_id = b.id;

name  sector_id  sector_name
----  ---------  -----------
A     1          X
B     2          Y

Without the OUTER keyword, the join is an inner join.