SELECT STATEMENT and FROM CLAUSE


The Oracle SELECT statement is used to retrieve records from one or more tables in an Oracle database.

SYNTAX

The syntax for the SELECT statement in Oracle/PLSQL is:

SELECT expressions
FROM tables
WHERE conditions;

Parameters or Arguments

expressions
The columns or calculations that you wish to retrieve.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
conditions
The conditions that must be met for the records to be selected.

EXAMPLE - SELECT ALL FIELDS FROM ONE TABLE

Let's look at how to use an Oracle SELECT query to select all fields from a table.

SELECT *
FROM homes
WHERE bathrooms >= 2
ORDER BY home_type ASC;

In this Oracle SELECT statement example, we've used * to signify that we wish to select all fields from the homes table where the number of bathrooms is greater than or equal to 2. The result set is sorted by home_type in ascending order.

EXAMPLE - SELECT INDIVIDUAL FIELDS FROM ONE TABLE

You can also use the Oracle SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT home_id, home_type, bathrooms
FROM homes
WHERE home_id < 500
AND home_type = 'two-storey'
ORDER BY home_type ASC, bathrooms DESC;

This Oracle SELECT example would return only the home_id, home_type, and bathrooms fields from the homes table where thehome_id is less than 500 and the home_type is 'two-storey'. The results are sorted by home_type in ascending order and thenbathrooms in descending order.

EXAMPLE - SELECT FIELDS FROM MULTIPLE TABLES

You can also use the Oracle SELECT statement to retrieve fields from multiple tables by using a join.

SELECT homes.home_id, customers.customer_name
FROM customers
INNER JOIN homes
ON customers.customer_id = homes.customer_id
ORDER BY home_id;

This Oracle SELECT example joins two tables together to gives us a result set that displays the home_id and customer_name fields where the customer_id value matches in both the customers and homes table. The results are sorted by home_id in ascending order.

PRACTICE EXERCISE #1:

Based on the contacts table below, select all fields from the contacts table whose last_name is 'Smith', contact_id is greater than or equal 1000 and contact_id is less than or equal to 2000 (no sorting is required):

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(2),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

Solution for Practice Exercise #1:

The following Oracle SELECT statement would select these records from the employees table:

SELECT *
FROM contacts
WHERE last_name = 'Smith'
AND contact_id >= 1000
AND contact_id <= 2000;

Or you could write the solution using the BETWEEN clause as follows:

SELECT *
FROM contacts
WHERE last_name = 'Smith'
AND contact_id BETWEEN 1000 AND 2000;

FROM CLAUSE

The Oracle/PLSQL FROM clause is used to list the tables and any join information required for the Oracle query.

SYNTAX

The syntax for the FROM clause in Oracle/PLSQL is:

FROM table1
[ { INNER JOIN
  | LEFT [OUTER] JOIN
  | RIGHT [OUTER] JOIN
  | FULL [OUTER] JOIN } table2
ON table1.column1 = table2.column1 ]

Parameters or Arguments

table1 and table2
These are the tables used in the SQL statement. The two tables are joined based on table1.column1 = table2.column1.

Note:

  • There must be at least one table listed in the FROM clause in Oracle/PLSQL.
  • If there are two or more tables listed in the FROM clause, these tables are generally joined in the FROM clause using INNER or OUTER joins. Although the tables can also be joined using the old syntax in the WHERE clause, we recommend using new standards and including your join information in the FROM clause. 

EXAMPLE - WITH ONE TABLE

It is difficult to explain the syntax for the Oracle FROM clause, so let's look at some examples.

We'll start by looking at how to use the FROM clause with only a single table.

For example:

SELECT *
FROM homes
WHERE bathrooms >= 2
ORDER BY home_type ASC;

In this Oracle FROM clause example, we've used the FROM clause to list the table called homes. There are no joins performed since we are only using one table.

EXAMPLE - TWO TABLES WITH INNER JOIN

Let's look at how to use the FROM clause with two tables and an INNER JOIN.

For example:

SELECT homes.home_id, customers.last_name, customers.first_name
FROM customers
INNER JOIN homes
ON customers.customer_id = homes.customer_id
ORDER BY home_id;

This Oracle FROM clause example uses the FROM clause to list two tables - customers and homes. And we are using the FROM clause to specify an INNER JOIN between the customers and homes tables based on the customer_id column in both tables.

EXAMPLE - TWO TABLES WITH OUTER JOIN

Let's look at how to use the FROM clause when we join two tables together using an OUTER JOIN. In this case, we will look at the LEFT OUTER JOIN.

For example:

SELECT customers.customer_id, contacts.last_name, contacts.first_name
FROM customers
LEFT OUTER JOIN contacts
ON customers.customer_id = contacts.contact_id
WHERE customers.last_name = 'Smith';

This Oracle FROM clause example uses the FROM clause to list two tables - customers and contacts. And we are using the FROM clause to specify a LEFT OUTER JOIN between the customers and contacts tables based on the customer_id column in both tables.

 

Source: www.techonthenet.com


Advertisements