Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Joins vs. Sub-Selects

Joins vs. Sub-Selects

From: <perryv_at_gmail.com>
Date: Thu, 19 Jul 2007 08:18:44 -0700
Message-ID: <1184858324.825144.238180@x40g2000prg.googlegroups.com>


Which relational operation perform faster in general, JOINs or SELECT subqueries?

Suppose we have these tables:

create table departments (

    dept_id int not null primary key auto_increment,     dept varchar(50) not null unique
);

create table employees (

    emp_id int not null primary key auto_increment,     name varchar(50) not null unique,     dept_id int not null,
    foreign key (dept_id) references departments (dept_id) );

create table websites (

    emp_id int not null primary key,
    url varchar(50) not null,
    foreign key (emp_id) references employees (emp_id) );

An employee belongs to exactly one department, but not all employees have a website.

Suppose I want to know the name, department and website of all employees. There are many ways to formulate this query in SQL. In particular, one possible query is this:

select name, dept, url

   from employees inner join departments

       on employees.dept_id = departments.dept_id
          left join websites
              on employees.emp_id = websites.emp_id;

Another possible one is this:

select name,

   (select dept from departments where dept_id = employees.dept_id) as dept,

   (select url from websites where emp_id = employees.emp_id) as url  from employees

Which of these two queries would execute faster?

Here are some sample data:

"departments" table:
+---------+------------+
| dept_id | dept |

+---------+------------+
| 1 | Marketing |
| 2 | Accounting |
| 3 | MIS |

+---------+------------+

"employees" table:
+--------+---------+---------+
| emp_id | name | dept_id |

+--------+---------+---------+
| 1 | Anne | 1 |
| 2 | Tim | 3 |
| 3 | Brad | 3 |
| 4 | Shirley | 2 |

+--------+---------+---------+

"websites" table:

+--------+-------------------+

| emp_id | url |
+--------+-------------------+
| 1 | www.anne-site.com |
| 3 | www.bradguru.com |
+--------+-------------------+

Sample query result:

+---------+------------+-------------------+

| name | dept | url |
+---------+------------+-------------------+
| Anne | Marketing | www.anne-site.com |
| Tim | MIS | NULL |
| Brad | MIS | www.bradguru.com |
| Shirley | Accounting | NULL |
+---------+------------+-------------------+
Received on Thu Jul 19 2007 - 10:18:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US