Self Join
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 condition. To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition.
Example:
SELECT WORKER.ENAME || ' WORKS FOR ' || MANAGER.ENAME "WORKS FOR"
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR = MANAGER.EMPNO; WORKS FOR
———————————
SMITH WORKS FOR FORD
ALLEN WORKS FOR BLAKE
WARD WORKS FOR BLAKE
JONES WORKS FOR KING
MARTIN WORKS FOR BLAKE
BLAKE WORKS FOR KING
CLARK WORKS FOR KING
SCOTT WORKS FOR JONES
TURNER WORKS FOR BLAKE
ADAMS WORKS FOR SCOTT
JAMES WORKS FOR BLAKE
FORD WORKS FOR JONES
MILLER WORKS FOR CLARK
13 rows selected.
Outer Join
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
Right Outer Join
To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
Example:
SELECT E.ENAME, E.MGR, E.DEPTNO, D.DEPTNO
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
ORDER BY E.DEPTNO;
(OR)
SELECT E.ENAME, E.MGR, E.DEPTNO, D.DEPTNO
FROM EMP E RIGHT OUTER JOINDEPT D
ON E.DEPTNO = D.DEPTNO
ORDER BY E.DEPTNO;
ENAME MGR E.DEPTNO D.DEPTNO
———- ——- ——- —— —————
CLARK 7839 10 10
MILLER 7782 10 10
KING 10 10
JONES 7839 20 20
SMITH 7902 20 20
SCOTT 7566 20 20
FORD 7566 20 20
ADAMS 7788 20 20
WARD 7698 30 30
TURNER 7698 30 30
ALLEN 7698 30 30
JAMES 7698 30 30
MARTIN 7698 30 30
BLAKE 7839 30 30
40 —— Extra row fetch second table dept.
15 rows selected.
Left Outer Join:
To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.
Example:
SELECT E.ENAME, E.MGR, E.DEPTNO, D.DEPTNO
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO (+)
ORDER BY E.DEPTNO;
(OR)
SELECT E.ENAME, E.MGR, E.DEPTNO, D.DEPTNO
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
ORDER BY E.DEPTNO; ENAME MGR E.DEPTNO D.DEPTNO
———– ——- ————— —————-
CLARK 7839 10 10
KING 10 10
MILLER 7782 10 10
JONES 7839 20 20
FORD 7566 20 20
ADAMS 7788 20 20
SMITH 7902 20 20
SCOTT 7566 20 20
WARD 7698 30 30
TURNER 7698 30 30
ALLEN 7698 30 30
JAMES 7698 30 30
BLAKE 7839 30 30
MARTIN 7698 30 30
14 rows selected.
Full Outer Join:
To write a query that performs an outer join 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 FULL [OUTER] JOIN syntax in the FROM clause.
Example:
SELECT E.ENAME, E.MGR, E.DEPTNO, D.DEPTNO
FROM EMP E FULL OUTER JOIN DEPT D
ON E.DEPTNO= D.DEPTNO; ENAME MGR E.DEPTNO D.DEPTNO
———– ——- ————— —————-
SMITH 7902 20 20
ALLEN 7698 30 30
WARD 7698 30 30
JONES 7839 20 20
MARTIN 7698 30 30
BLAKE 7839 30 30
CLARK 7839 10 10
SCOTT 7566 20 20
KING 10 10
TURNER 7698 30 30
ADAMS 7788 20 20
JAMES 7698 30 30
FORD 7566 20 20
MILLER 7782 10 10
40
15 rows selected.
Constraints.
Not Null Constraint:
A NOT NULL constraint requires a column of a table contain no null values. This is enforced at a time of insert and update query.
Syntax:
ALTER TABLE (TABLE_NAME)
MODIFY (COLUMN_NAME) CONSTRAINT (NOT_NULL_CONST_NAME)
NOT NULL;
Example:
ALTER TABLE EMP
MODIFY ENAME CONSTRAINT “NN_EMP_01”
NOT NULL;
Check Constraint:
This constraint validates incoming columns at row INSERT/UPDATE time. A CHECK integrity constraint on a column or set of columns requires that a specified condition be true or unknown for every row of the table. If a DML statement results in the condition of the CHECK constraint evaluating to false, then the statement is rolled back.
The CHECK constraint had some limitations:
It must be a Boolean expression evaluated using the values in the row being inserted
or updated, and
It cannot contain sub queries, sequences and the SQL function.
Syntax:
ALTER TABLE (TABLE_NAME)
ADD CONSTRAINT (CHECK_CONST_NAME)
CHECK (COLUMN_NAME WITH CONDITION) [DISABLE];
The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword,
the constraint will be created, but the condition will not be check.
Example:
ALTER TABLE EMP
ADD CONSTRAINT “CK_EMP_01”
CHECK (DEPTNO IN (10, 20, 30, 40));
Unique Key Constraint:
This constraint is used to ensure that all column values within a table never contain a duplicate entry. If the UNIQUE key consists of more than one column, that group of columns is said to be a composite unique key.
Syntax:
Single Unique Key:
ALTER TABLE (TABLE_NAME)
ADD CONSTRAINT (UNIQUE_KEY_CONST_NAME)
UNIQUE (COLUMN_NAME);
Composite Unique Key:
ALTER TABLE (TABLE_NAME)
ADD CONSTRAINT (UNIQUE_KEY_CONSTRAINT_NAME)
UNIQUE (COLUMN_NAME_1, COLUMN_NAME_2,……COLUMN_NAME_N);
Example:
ALTER TABLE EMP
ADD CONSTRAINT “UK_EMP_01”
UNIQUE (EMPNO);
Primary Key Constraint:
This constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this constraint will create a unique index on the target primary key. Each table in the database can have at most one PRIMARY KEY constraint. Although it is not required, every table should have a primary key.
The PRIMARY KEY integrity constraint guarantees that both of the following are true:
No two rows of a table have duplicate values in the specified column or set of columns.The primary key columns do not allow nulls. That is, a value must exist for the primary key columns in each row.
Syntax:
Single Unique Key:
ALTER TABLE (TABLE_NAME)
ADD CONSTRAINT (PRIMARY_KEY_CONST_NAME)
PRIMARY KEY (FIELD_NAME);
Composite Unique Key:
ALTER TABLE (TABLE_NAME)
ADD CONSTRAINT (PRIMARY_KEY_CONST_NAME)
PRIMARY KEY (COLUMN_NAME_1, COLUMN_NAME_2,……COLUMN_NAME_N);
Example:
ALTER TABLE EMP
ADD CONSTRAINT “PK_EMP_01”
PRIMARY KEY (EMPNO);
References Constraint:
Whenever two tables contain one or more common columns, Oracle can enforce the relationship between the two tables through a referential integrity constraint. Define a PRIMARY or UNIQUE key constraint on the column in the parent table (the one that has the complete set of column values). Define a FOREIGN KEY constraint on the column in the child table (the one whose values must refer to existing values in the parent table).
Reference constraint is only applied at SQL insert and deletes times. At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.
Syntax:
ALTER TABLE (TABLE_NAME)
ADD CONSTRAINT (CHECK_CONST_NAME)
FOREIGN KEY (COLUMN_NAME)
REFERENCES (PRIMARY_TABLE_NAME)
(PRIMARY_TABLE_COLUMN_NAME);
Example:
ALTER TABLE EMP
ADD CONSTRAINT “FK_EMP_01”
FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO);
Difference between the HAVING and WHERE Clauses
Though the HAVING clause specifies a condition that is similar to the purpose of a
WHERE clause, the two clauses are not interchangeable.
Listed below are some differences to help distinguish between the two:
The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
The WHERE clause selects rows before grouping. The HAVING clause selects
rows after grouping.
The WHERE clause cannot contain aggregate functions (COUNT, MAX, MIN, SUM, AVG, etc…) but in the HAVING clause can contain aggregate functions.
Difference between UNIQUE and PRIMARY KEY constraints
A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys.
The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.PRIMARY KEY cannot contain NULL value but UNIQUE keys can contain null values.
Difference between UNION and UNION ALL constraints
The main difference is that UNION will eliminate any duplicate rows returned by the various select statements being unioned whereas UNION ALL will not. It will return all records even duplicates or we can simply say, UNION is used to select distinct values from two tables where as UNION ALL is used to select all values including duplicates from the tables.
In Oracle, UNION does not support BLOB (or CLOB) |
