Things i learn and wanna share

In SQL the select statement is used to combine (join) records stored in different tables in order to display more meaningful and complete information:

select [distinct] [<alias ak>.]<column i>, . . . , [<alias al>.]<column j>

from <table 1> [<alias a1>], . . . , <table n> [<alias an>]

[where <condition>]

We can use alias when two or more columns have the same name in different tables.

Or simply we can add the complete relation name can be put in front of the column

i.e. table_name.column_name

Joining Relations

Comparisons in the where clause are used to combine rows from different tables.

Example: In the table EMP only the numbers of the departments are stored, not their name. For each salesman, we now want to retrieve the name as well as the number and the name of the department where he is working:

select ENAME, E.DEPTNO, DNAME

from EMP E, DEPT D

where E.DEPTNO = D.DEPTNO

and JOB = ’SALESMAN’;

Explanation: E and D are table aliases for EMP and DEPT, respectively. The computation of the query result occurs in the following manner (without optimization):

1. Each row from the table EMP is combined with each row from the table DEPT (this operation

is called Cartesian product). If EMP contains m rows and DEPT contains n rows, we thus get n _ m rows.

2. From these rows those that have the same department number are selected (where

E.DEPTNO = D.DEPTNO).

3. From this result finally all rows are selected for which the condition JOB = ’SALESMAN’ holds.

The joining condition for the two tables is based on the equality operator “=”. The columns compared by this operator are called join columns and the

join operation is called an “equijoin” ,any number of tables can be combined in a select statement. It is even possible to join a table with itself “selfjoin”

Subqueries

queries can be used for assignments to columns. A query result can also be used in a condition of a where clause. In such a case the query is called a subquery and the complete select statement is called a nested query.

A respective condition in the where clause then can have one of the following forms:

1. Set-valued subqueries

<expression> [not] in ( <subquery> )

<expression> <comparison operator> [any|all] ( <subquery> )

An <expression> can either be a column or a computed value.

2. Test for (non)existence

[not] exists ( <subquery> )

In a where clause conditions using subqueries can be combined arbitrarily by using the logical connectives and and or.

While using subquery we can use “=” if the result is only one, otherwise if we are not sure of how many results we should use “in”.

An important class of subqueries are those that refer to its surrounding (sub)query and the tables listed in the from clause, respectively. Such type of queries is called correlated subqueries.

Any & all: Conditions of the form

<expression> <comparison operator> [any|all] <subquery>

are used to compare a given <expression> with each value selected by <subquery>

• For the clause any, the condition evaluates to true if there exists at least on row selected by the subquery for which the comparison holds. If the subquery yields an empty result set, the condition is not satisfied.

• For the clause all, in contrast, the condition evaluates to true if for all rows selected by the subquery the comparison holds. In this case the condition evaluates to true if the subquery does not yield any row or value.

For all and any, the following equivalences hold:

in , = any

not in , != all

Often a query result depends on whether certain rows do (not) exist in (other) tables. Such type of queries is formulated using the exists operator.