SQL- Sub Queries

A subquery is a type of SQL query, where a query is embedded within another query. Sub-queries are very powerful. To help you understand a subquery consider the following SELECT statement to retrieve the details of employees who belong to department 30.

SELECT * FROM EMPLOYEES

WHERE DEPARTMENT_ID=30;

In the above query, the department ID value has been provided, and is used on the right hand side of the WHERE condition. However, such constant values might not also be provided or known. For example, consider the query re-phrased as - retrieve the details of employees who belong to the same department as 'Alexander Khoo'. Here the department number has not been provided. Instead the name of an employee is given. Using this name, you would need to first find out - to which department does Alexander Khoo belong. Let say this is some value 'X'. You would have to proceed further to find out all the other employees who belong to the department X.

If you notice this is a 2-step process involving:

SQL Joins

SQL Joins

Tables are joined in Oracle when data must be retrieved from more than one table. As a result of normalization the tables in a database are stored in their simplest forms with little or no redundancy. This results in separating data based on objects and still maintained relationships based on the values in the tables. Joining tables is an essential part of learning how to query the database, because most of the time all the data is not present in one table. Everytime you need to read information that is stored in multiple table, you will need to perform a join operation. In the screenshot below, you are looking at two tables called COUNTRIES and REGIONS. The two countries are related based on the Region_ID column. For example the Region whose ID is 1, and name is Europe, is made up of the countries Belgium, Switzerland, Germany, Denmark, and France in the Countries table. All these countries have their REGION_ID column set to 1.

If we want to see an output based on these two tables, displaying the Region_Name and Country_Name, you would need to join the two tables using the relationship created by the REGION_ID column.

There are different types of joins in Oracle, namely: