Ever get confused by the difference between an INNER JOIN or an OUTER JOIN?

How about a LEFT OUTER JOIN, a RIGHT OUTER JOIN or a FULL OUTER JOIN?

Here's a simple set of tables and data to help you understand! 

create table employee
(employee_id int
,employee_name varchar(30)
,department_id int
)

create table department
(department_id int
,department_name varchar(30)
)

Note the complete lack of referential integrity in the table definitions.  :(

insert into department values (1,'Crew')
insert into department values (2,'Passengers')
insert into department values (3,'Rescuers')

insert into employee values (1,'Gilligan',1)
insert into employee values (2,'Skipper',1)
insert into employee values (3,'Mary Ann',2)
insert into employee values (4,'Ginger',2)
insert into employee values (5,'Surfer',4)

Note that no employees are in department 3, Rescuers.

Note that there is no department 4.

Here come some sample queries: 

This will miss departments 3 and 4, plus the Surfer:
select
 e.employee_id, e.employee_name, e.department_id
,d.department_id, d.department_name
from employee e
inner join department d on e.department_id = d.department_id 

1   Gilligan   1   1   Crew
2   Skipper    1   1   Crew
3   Mary Ann   2   2   Passengers
4   Ginger     2   2   Passengers 

This will miss Department 3.

select
 e.employee_id, e.employee_name, e.department_id
,d.department_id, d.department_name
from employee e
left outer join department d on e.department_id = d.department_id

1    Gilligan  1    1    Crew
2    Skipper   1    1    Crew
3    Mary Ann  2    2    Passengers
4    Ginger    2    2    Passengers
5    Surfer    4    NULL NULL

This will miss the Surfer (and therefore Department 4). 

select
 e.employee_id, e.employee_name, e.department_id
,d.department_id, d.department_name
from employee e
right outer join department d on e.department_id = d.department_id

1       Gilligan  1    1    Crew
2       Skipper   1    1    Crew
3       Mary Ann  2    2    Passengers
4       Ginger    2    2    Passengers
NULL    NULL      NULL 3    Rescuers

This will miss nothing.

select
 e.employee_id, e.employee_name, e.department_id
,d.department_id, d.department_name
from employee e
full outer join department d on e.department_id = d.department_id

1       Gilligan   1        1      Crew
2       Skipper    1        1      Crew
3       Mary Ann   2        2      Passengers
4       Ginger     2        2      Passengers
5       Surfer     4        NULL   NULL
NULL    NULL       NULL     3      Rescuers

This will miss everything but Department 4 and the Surfer: 

select
 e.employee_id, e.employee_name, e.department_id
,d.department_id, d.department_name
from employee e
left outer join department d on e.department_id = d.department_id
where d.department_id is null
 

5    Surfer  4   NULL  NULL

This kind of experiment only takes a few minutes to do.  With a bit of care in creating reasonable test data (taking into account things that can be null or the possibility of bad data), it's often very easy to prove that you are (or just as important, are not!) getting the correct results with your test queries. 

If you are still stuck on how to write the statement you need, here are a couple of important points to consider when you ask for help:

1. Include sample create table statements.   That way, we don't have to guess. :)
2. For best results, include a set of insert statements that load sample data.
3. Specify which rows in the sample output are the ones that should be returned from the query

4. Give us your best shot.  We're all much more likely to help you out if we see you've been doing some work!  Otherwise, we might mistake you for a lazy college student trying to get us to do their homework for them - and we don't like to do that. (That's because they graduate, get a job with our company, and then we have to do even more of their work!)
   

The above process removes huge amounts of ambiguity and makes it very fast for someone to help out. ;)

And for you to be sure they were right! 

Can u pls tell me which join is more time consuming? 

binobose:

Can u pls tell me if which join is more time consuming? 

Honestly, I don't think it matters.

You either need the set of data that the particular join type returns, or you don't.  If you need it, that's what you need to do. :)

A Full Outer Join (which is a left outer join and a right outer join at the same time) will take more time simply because it has more data to return and from two different starting points.  Otherwise, no difference.

 Thanx :)

Hello all,

If execution time is an issue and I believe it is, then be sure that the joins are over columns that are indexed and the resultant query is using an index that gives the best execution performance over different query filter parameters.

http://www.kodyaz.com

eralper:

Hello all,

If execution time is an issue and I believe it is, then be sure that the joins are over columns that are indexed and the resultant query is using an index that gives the best execution performance over different query filter parameters.

I happen to agree with your performance goals, but performance tuning is out of scope for this how-to. 

That's because it's not always such a cut and dried decision.

For example, programmers are often presented with a data model that they must use, and which they have no authority over.  We can ask for indexes to be created but we cannot force them to exist under those circumstances.  We are responsible for returning correct results from our queries, and then, only if possible, fast results.

Second, different vendors have different sql execution algorithms.  To be more precise, different vendor software sub-sub-sub versions can have different sql execution algorithms.  The order in which the tables are listed in the query can affect performance.  The order in which the different filters in the where clause are listed can affect performance.  And, in the next release, the recommended order may literally be reversed.  (And did I have some choice words about Oracle Corporation when they did that way back when!)   Moving a where clause item to the join's on clause can affect performance.

The single best way to improve query performance is good database design. ;)    And sadly, that is often not going to happen.  Good data modelers are a rare breed.

That said, paying proper attention to the existence of indexes is definitely a "best practice". 

   

 

it is real good topic for a beginner

Like David said that performance depends on multi factors the top most being a good database design.

I'll try to explain a couple of items that can help you acheive good performance when using Joins. But before I do that I will explain the two basic types of indexes. Even though each vendor creates/manages and uses indexes differently, you have one type of index that is most commonly used with primary key of the table (clustered index). The rows of the table are actually stored physically in the order of the index. The other type of index simply keeps track of what row/data is where on the disk/storage (non-clustered index).

Essentially if you know the primary key value and give it to the database engine.. more like "select myid, myname from myid = 100000" database engine can quickly read the row directly as the db engine can easily skip a large set of number and quickly get to the value 10000 and get the exact physical location, but if you use a query like "select myid, myname from myname = 'ish'" (and assuming you have a non clustered index on myname column) then the database uses the index to find the location of the row. This type of index may result in a higher number of scans needed by the engine to find the actual physical position of the row.

So if you were to create a join between two tables on columns that are indexed using clustered index, that probably will be the fastest. But if you are using one table with primary key and another table with foreign key, it may be a little bit slower than the first one. Now if your foreign key column is not indexed at all it might be even slower, but it is not necessary. Really depends on the size of the table, and other factors combined. Now if you create a join on column(s) that are not indexed at all then that would be the first query to cause problems as the amount of data increases.

These are of course generalized examples to illustrate the concept in rather plain words :)

HTH,

Ish

Quick Objects

Fastest Way To Powerful Applications

 

Oracle has a bit-mapped index that is quite handy for fields with a limited set of values in them. Non-intuitively, it is sometimes faster NOT to use the indexes. It depends upon the proportion of records to be brought back. The higher the percentage of records in the table to be brought back, the less useful the indexes are.