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
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
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
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
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:
Include sample create table statements. That way, we don't have to guess. :)
For best results, include a set of insert statements that load sample data.
Specify which rows in the sample output are the ones that should be returned from the query
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!
sqlsql serversqldatabaseSQL Server 2000
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
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.
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
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.
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".
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
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 :)
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.
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
david wendel...
All-Star
15865 Points
2243 Posts
HOW DO INNER JOINS AND OUTER JOINS WORK?
Nov 10, 2007 01:44 AM|LINK
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:
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!
sql sql server sqldatabase SQL Server 2000
binobose
Contributor
2728 Points
609 Posts
Re: HOW DO INNER JOINS AND OUTER JOINS WORK?
Nov 30, 2007 02:03 AM|LINK
Can u pls tell me which join is more time consuming?
Bino
www.codepal.co.in
Please Mark as Answer if you find the post useful.
david wendel...
All-Star
15865 Points
2243 Posts
Re: HOW DO INNER JOINS AND OUTER JOINS WORK?
Nov 30, 2007 03:51 AM|LINK
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.
binobose
Contributor
2728 Points
609 Posts
Re: HOW DO INNER JOINS AND OUTER JOINS WORK?
Dec 03, 2007 03:56 AM|LINK
Thanx :)
Bino
www.codepal.co.in
Please Mark as Answer if you find the post useful.
eralper
Contributor
6048 Points
971 Posts
Re: HOW DO INNER JOINS AND OUTER JOINS WORK?
Apr 11, 2008 05:28 AM|LINK
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
SQL Server 2012
david wendel...
All-Star
15865 Points
2243 Posts
Re: HOW DO INNER JOINS AND OUTER JOINS WORK?
Apr 11, 2008 06:24 AM|LINK
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".
uwspstar
Member
740 Points
215 Posts
Re: HOW DO INNER JOINS AND OUTER JOINS WORK?
Apr 11, 2008 02:31 PM|LINK
it is real good topic for a beginner
If you mark as "Answer"other people can use this answer as a reference
ishsingh
Member
120 Points
15 Posts
Re: HOW DO INNER JOINS AND OUTER JOINS WORK?
Apr 19, 2008 08:08 AM|LINK
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
sql database SQL and more
Quick Objects - Powerful, Flexible, Free Business Logic Framework
** If an answers solves your problem don't forget to use "Mark as Answer" on the post that helped you. **
david wendel...
All-Star
15865 Points
2243 Posts
Re: HOW DO INNER JOINS AND OUTER JOINS WORK?
Apr 21, 2008 06:16 AM|LINK
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.sirdneo
All-Star
15171 Points
2509 Posts
Re: HOW DO INNER JOINS AND OUTER JOINS WORK?
Dec 17, 2008 10:19 AM|LINK
Nice article , I think you should post it in articles section.
database
Zeeshan Umar
~Please Mark As Answer, one or multiple posts, which helped you. So that it might be useful for others~