Javenue logo

Javenue

Программирование на Java

Информационные технологии

Inner Join and Outer Join in SQL - overview and examples

[Перевод статьи можно найти здесь.]

SQL keyword join is used in select queries to combine results from several tables in one result set. Of course, tables' consistency is not violated. There are three main types of join-expressions:

  • inner join;
  • outer join;
  • cross join;

Outer join could be one of - left, right, or full. Almost in all DBs the keyword "outer" can be omitted.

The join clause looks like this:

... join_type join table_name on condition ...

It is rather obvious:

  • join_type - could be one of inner, left, right, or full;
  • table_name - name of the table which will be joined to the result set;
  • condition - the condition of joining tables;

Join clause is situated right after select-from part. You can use as many join clauses as you wish. Classic situation is joining a table to another one based on the foreign key.

Setting up the tables

Let us create two tables in Oracle DB and construct different join expressions to retrieve data from them. Note, that syntax shown below is applicable to the most popular databases.

The first table will store user's ID and nickname. The second table will store resource info: ID, name, and ID of the user who has permission to somehow administrate the resource. See the DML for both tables below:

create table t_users (
t_id number(11, 0),
t_nick varchar(16),
primary key (t_id) )

create table t_resources (
t_id number(11, 0),
t_name varchar(16),
t_userid number (11, 0),
primary key (t_id) )

And the data will be as following:

T_ID  T_NICK
  1    user1
  3    user3
  4    user4

T_ID  T_NAME  T_USERID
  1    res1     3
  2    res2     1
  3    res3     2
  5    res5     3

Inner join example

Inner join is used to retain those entries from the first table which have corresponding entries in the second table. In other words, "condition" should always be true in order for entry to be present in result set. Example:

select t_resources.t_name, t_users.t_nick
  from t_resources
  inner join t_users on t_users.t_id = t_resources.t_userid

And the result will be:

 T_NAME  T_NICK
  res2    user1
  res1    user3
  res5    user3

Left join

Outer joins does not require records from the first table to have matches in the second table.

The left join will get all entries from the first table even if there are no corresponding entries in the second table. In other words, "condition" does not consider the joined (right) table. For example:

select t_resources.t_name, t_users.t_nick
  from t_resources
  left join t_users on t_users.t_id = t_resources.t_userid

And the result of query execution:

 T_NAME  T_NICK
  res1    user3
  res2    user1
  res3    (null)
  res5    user3

So, we'll get all resources and their administrators even if there are no admins for the particular resource.

Right join example

On the other hand, right join will retain all entries from joined table even if there are no matching entries in the main table. For instance,

select t_resources.t_name, t_users.t_nick
  from t_resources
  right join t_users on t_users.t_id = t_resources.t_userid

And the result is:

 T_NAME  T_NICK
  res2    user1
  res1    user3
  res5    user3
  (null)  user4

Again, the result set will show resources and the administrators. If administrator is not occupied, the entry will be displayed nevertheless. Probably, the resource could have been deleted.

SQL full join

Full outer join will try to match all the entries from both tables. In other words, this is intersection of left and right joins. Consider this expression:

select t_resources.t_name, t_users.t_nick
  from t_resources
  full join t_users on t_users.t_id = t_resources.t_userid

And the result set will be:

 T_NAME  T_NICK
  res1      user3
  res2      user1
  res3      (null)
  res5      user3
 (null)     user4

Note, some Database Management Systems do not support this functionality (MySQL, for instance). In that case just combine result sets of two queries using union:

select t_resources.t_name, t_users.t_nick
  from t_resources
  left join t_users on t_users.t_id = t_resources.t_userid
union
select t_resources.t_name, t_users.t_nick
  from t_resources
  right join t_users on t_users.t_id = t_resources.t_userid

Cross join example

Finally, cross join, also know as cartesian product. Use it with care, since query execution time will grow in nonlinear manner when row count is increased. Example of query which is equivalent to the cross join:

select t_resources.t_name, t_users.t_nick
  from t_resources, t_users

Popular SQL interview question

The popular SQL interview question you will probably encounter.

Assume you have two tables with "str" columns defined as varchar. How to get all rows from the first table which do not have corresponding entry in the other table? The join will be helpful here:

select * from t1
left join t2 on t1.str = t2.str
where t2.str is null

Join is a powerful tool to be aware of. Hope, the article helped you to understand the basics.


Комментариев: 0

  Выйти

  * для публикации комментариев нужно