Data Selection Using SQL

Inner Join or Equijoin

Joining two or more tables together is the best relational database usage. You relate the tables using the WHERE clause. The equal sign (=) in a query only retrieves records that have exact match. This is an example of the inner join or equijoin. Query the employees name and their department name from employee and department tables.

Use the previous query and use table aliases to avoid the ambiguous column. Use the letter “e” for the employee table alias and the letter “d” for the department table alias.

Cartesian product

A “Cartesian” product is caused by joining “N” number of tables while you have less than “N-1” joins condition in the query. This is an example of a Cartesian product.

Outer Join

By joining two or more tables using OUTER join, not only you retrieve all matching records but also you retrieve the records that do not match. For example that you may have an employee that you did not assign any department number for him or via versa.

First query the department table.

Note, there are six records.

Insert a record to the department. Remember that we have no employee in the department table and save the insert transaction.

Now, query all the employee names and their department names including the entire department name with no employees. As you know there is no employee in the "Finance" department. Use + sign next to the column that has no match in it.

Self Join

If a table refers to itself in the WHERE clause, we say that join is a selfjoin. Query the manager names with their employees sorted by the manager names. This is an example of a selfjoin.

Data Selection using SUM, AVG, MIN, and MAX functions

Query the department number and their total, average, min, and max salaries for each department.

IN clause

Use the IN clause, when you are going to use OR. Query the employee names, Department IDs that work for the IT or Audit departments (the department number 1 or 3)

Sub-query

If you write a query within a query, you are using sub-query. In the WHERE clause, not all the time, you have a constant value to compare. If you have to query a value from table, then you need to write a sub-query. Query the employee names that work in the "IT" department. Assuming the department number is unknown.