SQL Joins



Cross Join (ie no join)

SELECT * FROM emp , dept;
SELECT * FROM employee CROSS JOIN department;
Graphically, if there is no relationship between the tables:
------------------------------------ ------------------------------------
alternatively if the cross join was explicity declared we could draw it explicitely:
------------------------------------
------------------------------------

Equi-join (typical join using equality )

SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno;

SELECT *
FROM employee INNER JOIN department
ON employee.deptno = department.deptno;
Graphically this can be drawn 4 ways depending on which fields, if any, have a unique index.
If both have unique indexes on the join field then it looks like
1. Dept Master, Emp Detail (one to many)
1. Unique index on dept.deptno
2. Non-unique index on emp.deptno  (or no index)
2. Emp Master, Dept Detail (one to many)
Doesn’t make sense, but
1. Unique index on emp.deptno
2. Non-unique index on dept.deptno  (or no index)
3. Emp and Dept one to one
Unique indexes on both
Emp.deptno and dept.deptno
Objects should be on a horizontal
Plane at same level
4. Emp and Dept many to many
No unique indexes
No order to layout
Natural Join  (implied join)

Same as equi join, just the join is implied, and join column is only returned once:

SELECT * FROM emp NATURAL JOIN dept

 Join on fields with same name 
DepartmentIDEmployee.LastNameDepartment.DepartmentName
34SmithClerical
33JonesEngineering
34RobinsonClerical
33SteinbergEngineering
31RaffertySales
graphically the same as an equi-join

Non-equi-join (unusual join using >, < or between) 

An nonequi (or theta) join is a join statement that uses an unequal operation (i.e: <>, >, <, !=, BETWEEN, etc.)
The majority of table joins are on field equality, but occasionally ranges are used. Ranges are most often used as filters on predicates such as  field > value. Ranges are used less often on field to field joins, but here is an example:

Select all the employees who started while employee 101 was working for the company (using Oracle's default HR schema)


SELECT e.employee_id,e.first_name, e.last_name
FROM employees e, job_history h
WHERE h.employee_id=101
  AND e.hire_date >= h.start_date
  AND e.hire_date <= h.end_date;

Non-equi-joins are multipliers either way - ie a row from "e" can join into many rows from "h" and vice versa. The only thing stopping it here is there is a filter on "h", ie "h.employee=101". Take that predicate filter off and we have a many to many
                  
See Bobby Durrett's blog on outer joins

If English and French both have a unique key on the "ordinal_id" then it's basically one-to-one relationship



We add an arrow in the middle of the line to denote "outer join". The arrow points from the table that drives the join, ie all the rows in the table pointed from are returned even if a match isn't found in the table pointed to
.

            
above graphic originally on http://blog.mclaughlinsoftware.com/oracle-sql-programming/basic-sql-join-semantics/

    type ANSI ANSI 89 (Oracle) type type
    inner join english INNER JOIN french
    using (ordinal_id)
    english e, french f 
    where e.ordinal_id=f.ordinal_id
    left outer join english LEFT JOIN french 
    using (ordinal_id)
    english e, french f 
    where e.ordinal_id=f.ordinal_id(+)
    right outer join english RIGHT JOIN french 
    using (ordinal_id)
    english e, french f 
    where e.ordinal_id(+)=f.ordinal_id
    full join  english FULL JOIN french 
    using (ordinal_id)
    english e, french f 
    where e.ordinal_id=f.ordinal_id(+)
    UNION
    english e, french f 
    where e.ordinal_id(+)=f.ordinal_id
        

Self Join


Exists/Not Exists (IN/Not IN)

Semi Joins

SELECT *
FROM dept d  WHERE existsSELECT null FROM emp e  WHERE e.deptno=d.deptno);

SELECT *
FROM dept d  WHERE d.deptno in SELECT  deptno FROM emp e  );



SELECT *
FROM dept d  WHERE not exists ( SELECT null FROM emp e  WHERE e.deptno=d.deptno);

SELECT *
FROM dept d  WHERE d.deptno not in ( SELECT deptno FROM emp e  );

Union/Intersect/Minus/Except

Generally graph as separate trees - good enough for now

In the future:
Except -> sometimes be made Not Exists
Intersect -> somtimes be made Exists

Minus (Oracle) = Except

Subqueries

subquery ( subselect used in where clause)


correlated subquery (subselect uses fields from outer query)

       These are are treated the same as far as diagramming goes:



scalar subquery (subselect in select list)

                    This example comes from http://linuxdevcenter.com/pub/a/linux/2004/01/06/rangekeyed_1.html

SELECT A.AUSR_LOGIN_SCREEN_NAME FROM ADDS_USERS A,
AUTHORIZED_IP_ADDRESSES B
WHERE A.AUSR_ID = B.AUSR_ID AND :B1 BETWEEN 
B.AIA_IP_ADDRESS_START AND
B.AIA_IP_ADDRESS_END;

SELECT
(select A.AUSR_LOGIN_SCREEN_NAME
from ADDS_USERS A

where a.ausr_id = b.ausr_id)
FROM AUTHORIZED_IP_ADDRESSES B
WHERE :B1 BETWEEN B.AIA_IP_ADDRESS_START AND B.AIA_IP_ADDRESS_END;

inline views (subselect in from clause)



Comments