0% found this document useful (0 votes)
164 views

SQL Joins

The document discusses different types of joins that can be performed between tables in a database, including: - Inner joins, which return only rows where the join condition is satisfied between two tables. - Outer joins, which return all rows of one table that meet the join condition plus related rows from another table, or return rows with null values if no match is found. - Self joins, which join a table to itself to relate columns within the same table, such as relating employees to their managers. - Equijoins, which use an equality operator in the join condition, and non-equijoins, which use other comparisons like between. The document provides examples of each type of

Uploaded by

chalamg
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
164 views

SQL Joins

The document discusses different types of joins that can be performed between tables in a database, including: - Inner joins, which return only rows where the join condition is satisfied between two tables. - Outer joins, which return all rows of one table that meet the join condition plus related rows from another table, or return rows with null values if no match is found. - Self joins, which join a table to itself to relate columns within the same table, such as relating employees to their managers. - Equijoins, which use an equality operator in the join condition, and non-equijoins, which use other comparisons like between. The document provides examples of each type of

Uploaded by

chalamg
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 5

1.6.

Joins Between Tables


Thus far, our queries have only accessed one table at a time. Queries can access
multiple tables at once, or access the same table in such a way that multiple r
ows of the table are being processed at the same time. A query that accesses mul
tiple rows of the same or different tables at one time is called a join query. F
or example, say you wish to list all the employee records together with the name
and location of the associated department. To do that, we need to compare the d
eptno column of each row of the emp table with the deptno column of all rows in
the dept table, and select the pairs of rows where these values match. This woul
d be accomplished by the following query:
SELECT emp.empno, emp.ename, emp.job, emp.hiredate, emp.sal, emp.deptno,
dept.dname, dept.loc
FROM emp, dept
WHERE emp.deptno=dept.deptno;
empno | ename | job | hiredate | sal | deptno | dname | loc
-------+--------+-----------+-----------+---------+---------+--------+--------+-
---------
7782 | CLARK | MANAGER | 09-JUN-81 | 2450.00 | 10 | ACCOUNTING | NEW YO
RK
7839 | KING | PRESIDENT | 17-NOV-81 | 5000.00 | 10 | ACCOUNTING | NEW YO
RK
7934 | MILLER | CLERK | 23-JAN-82 | 1300.00 | 10 | ACCOUNTING | NEW YO
RK
7369 | SMITH | CLERK | 17-DEC-80 | 800.00 | 20 | RESEARCH | DALLAS
7566 | JONES | MANAGER | 02-APR-81 | 2975.00 | 20 | RESEARCH | DALLAS
7788 | SCOTT | ANALYST | 19-APR-87 | 3000.00 | 20 | RESEARCH | DALLAS
7876 | ADAMS | CLERK | 23-MAY-87 | 1100.00 | 20 | RESEARCH | DALLAS
7902 | FORD | ANALYST | 03-DEC-81 | 3000.00 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 20-FEB-81 | 1600.00 | 30 | SALES | CHICAG
O
7521 | WARD | SALESMAN | 22-FEB-81 | 1250.00 | 30 | SALES | CHICAG
O
7654 | MARTIN | SALESMAN | 28-SEP-81 | 1250.00 | 30 | SALES | CHICAG
O
7698 | BLAKE | MANAGER | 01-MAY-81 | 2850.00 | 30 | SALES | CHICAG
O
7844 | TURNER | SALESMAN | 08-SEP-81 | 1500.00 | 30 | SALES | CHICAG
O
7900 | JAMES | CLERK | 03-DEC-81 | 950.00 | 30 | SALES | CHICAG
O
(14 rows)

Observe two things about the result set:

There is no result row for department 40. This is because there is no matching e
ntry in the emp table for department 40, so the join ignores the unmatched rows
in the dept table. Shortly we will see how this can be fixed.
It is more desirable to list the output columns explicitly rather than using *:
SELECT empno, ename, sal, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;
Since all the columns had different names, the parser automatically found out wh
ich table they belong to, but it is a good style to fully qualify column names i
n join queries:
SELECT emp.empno, emp.ename, emp.sal, dept.dname, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;
Join queries of the kind seen thus far can also be written in this alternative f
orm:
SELECT * FROM dept
INNER JOIN emp ON (emp.deptno = dept.deptno);
This syntax is not as commonly used as the one above, but we show it here to hel
p you understand the following topics.
You will notice that in all the above results for joins no employees were return
ed that belonged to department # 40. Now we will figure out how we can get the d
epartment #40 record in. What we want the query to do is to scan the emp table a
nd for each row to find the matching deptno row. If no matching row is found we
want some "empty values" to be substituted for the emp table's columns. This kin
d of query is called an outer join. (The joins we have seen so far are inner joi
ns.) The command looks like this:
SELECT dept.deptno, emp.ename
FROM dept LEFT OUTER JOIN emp
ON dept.deptno = emp.deptno
ORDER BY dept.deptno;
deptno | ename | sal
--------+--------+---------
10 | CLARK | 2450.00
10 | KING | 5000.00
10 | MILLER | 1300.00
20 | SMITH | 800.00
20 | JONES | 2975.00
20 | SCOTT | 3000.00
20 | ADAMS | 1100.00
20 | FORD | 3000.00
30 | ALLEN | 1600.00
30 | WARD | 1250.00
30 | MARTIN | 1250.00
30 | BLAKE | 2850.00
30 | TURNER | 1500.00
30 | JAMES | 950.00
40 | |
(15 rows)
This query is called a left outer join because the table mentioned on the left o
f the join operator will have each of its rows in the output at least once, wher
eas the table on the right will only have those rows output that match some row
of the left table. When outputting a left-table row for which there is no right-
table match, empty (null) values are substituted for the right-table columns.
An alternative syntax of a left outer join is using the outer join operator (+)
to all the columns of the emp table in the join condition within the WHERE claus
e. So for all the rows in the dept table that have no matching rows in the emp t
able, EnterpriseDB returns null for any select list expressions containing colum
ns of emp. Hence the above example could be rewritten as:
SELECT dept.deptno, emp.ename
FROM dept, emp
WHERE dept.deptno = emp.deptno(+)
ORDER BY dept.deptno;

We can also join a table against itself. This is called a self join. As an examp
le, suppose we wish to find the name of each employee along with the name of tha
t employee's manager. So we need to compare the mgr column of each emp row to th
e empno column of all other emp rows.
SELECT e1.ename || ' works for ' || e2.ename AS "Employees and their Managers"
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno
Employees and their Managers
------------------------------
SCOTT works for JONES
FORD works for JONES
ALLEN works for BLAKE
WARD works for BLAKE
MARTIN works for BLAKE
TURNER works for BLAKE
JAMES works for BLAKE
MILLER works for CLARK
ADAMS works for SCOTT
JONES works for KING
BLAKE works for KING
CLARK works for KING
SMITH works for FORD
(13 rows)
Here we have re-labeled the emp table as e1 and e2 to be able to distinguish the
left and right side of the join. You can also use these kinds of aliases in oth
er queries to save some typing, e.g.:

SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno;
--------------------
Joins
A join is a query that combines rows from two or more tables, views, or material
ized views. Oracle performs a join whenever multiple tables appear in the query'
s FROM clause. The query's select list can select any columns from any of these
tables. If any two of these tables have a column name in common, you must qualif
y all references to these columns throughout the query with table names to avoid
ambiguity.
Join Conditions
Most join queries contain WHERE clause conditions that compare two columns, each
from a different table. Such a condition is called a join condition. To execute
a join, Oracle combines pairs of rows, each containing one row from each table,
for which the join condition evaluates to TRUE. The columns in the join conditi
ons need not also appear in the select list.
Equijoins
An equijoin is a join with a join condition containing an equality operator ( =
). An equijoin combines rows that have equivalent values for the specified colum
ns.
For example the following query returns empno,name,sal,deptno and department nam
e and city from department table.
select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept
where emp.deptno=dept.deptno;
The above query can also be written like, using aliases, given below.
select e.empno, e.ename, e.sal, e.deptno, d.dname, d.city from emp e, dept d whe
re emp.deptno=dept.deptno;
The above query can also be written like given below without using table qualif
iers.
select empno,ename,sal,dname,city from emp,dept where emp.deptno=dept.deptno;
And if you want to see all the columns of both tables then the query can be writ
ten like this.
select * from emp,dept where emp.deptno=dept.deptno;

Non Equi Joins.


Non equi joins is used to return result from two or more tables where exact join
is not possible.
For example we have emp table and salgrade table. The salgrade table contains gr
ade and their low salary and high salary. Suppose you want to find the grade of
employees based on their salaries then you can use NON EQUI join.
select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal betwe
en s.lowsal and s.hisal
Self Joins
A self join is a join of a table to itself. This table appears twice in the FROM
clause and is followed by table aliases that qualify column names in the join c
ondition. To perform a self join, Oracle combines and returns rows of the table
that satisfy the join condition.
For example the following query returns employee names and their manager names f
or whom they are working.
Select e.empno, e.ename, m.ename Manager from emp e,
emp m where e.mgrid=m.empno
Inner Join
An inner join (sometimes called a "simple join") is a join of two or more tables
that returns only those rows that satisfy the join condition.
Outer Joins
An outer join extends the result of a simple join. An outer join returns all row
s that satisfy the join condition and also returns some or all of those rows fro
m one table for which no rows from the other satisfy the join condition.
To write a query that performs an outer join of tables A and B and returns all r
ows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply
the outer join operator (+) to all columns of B in the join condition. For all r
ows in A that have no matching rows in B, Oracle returns null for any select lis
t expressions containing columns of B.
To write a query that performs an outer join of tables A and B and returns all r
ows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the
outer join operator (+) to all columns of A in the join condition. For all rows
in B that have no matching rows in A, Oracle returns null for any select list e
xpressions containing columns of A.
To write a query that performs an outer join and and returns all rows from A and
B, extended with nulls if they do not satisfy the join condition (a full outer
join), use the ANSI FULL [OUTER] JOIN syntax.
For example the following query returns all the employees and department names a
nd even those department names where no employee is working.
select e.empno,e.ename,e.sal,e.deptno,d.dname,d.city from emp e, dept d where e
.deptno(+)=d.deptno;
That is specify the (+) sign to the column which is lacking values.

You might also like