SQL Joins
SQL Joins
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;