Displaying Data from Multiple Tables

Obtaining Data from Multiple Tables :



Types of Joins :

• Joins that are compliant with the SQL:1999 standard include the following :

1. Cross joins
2. Natural joins
3. USING clause
4. Full (or two-sided) outer joins
5. Arbitrary join conditions for outer joins

Joining Tables Using SQL : 1999 Syntax

• Use a join to query data from more than one table

SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2];

Restricting Group Results

• In the same way that you use the WHERE clause to restrict the rows that you select, you use the HAVING clause to restrict groups

• To find the maximum salary in each of the departments that have a maximum salary greater than $10,000, you need to do the following :

1. Find the average salary for each department by groupingby department number
2. Restrict the groups to those departments with a maximum salary greater than $10,000

Restricting Group Results with the HAVING Clause :

• When you use the HAVING clause, the PostgreSQL server restricts groups as follows:

1. Rows are grouped
2. The group function is applied
3. Groups matching the HAVING clause are displayed

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
Example :

• SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)>4000 ;
• SELECT job, SUM(sal) as PAYROLL FROM emp WHERE job NOT LIKE '%MAN%' GROUP BY job HAVING SUM(sal) > 4000 ORDER BY SUM(sal);

SELECT column, group_function FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];