The SELECT statement is the most common command in SQL. It lets you retrieve information from a database.
There are 4 parts to the SELECT command:
* select - selects what you want to see (ie. the names of the columns in the tables)
* from - where you should get the information from (ie. the names of the tables)
* where - the selection criteria (ie. the conditions that must be met to restrict the data that is returned)
* order by - the sorting criteria (ie. a list of column names that control how the data is presented)
The SELECT command.
Of the above, only SELECT and FROM are manditory. The WHERE and ORDER BY clauses are optional.
You can use the SELECT statement to view the tables stored in your tablespace:
select * from user_tables;
SELECT command to retrieve table information on all tables.
This command can produce a large amount of output depending on the contents of your tablespace. Basically, however, it shows all the tables (specified by the "*") from the USER_TABLES system table. It displays a large amount of information about all the tables in your userspace.
This next example is very similar to the previous one:
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
CAR_NVINFO
CAR_VINFO
Example of extracting table names from the USER_TABLES system table.
The above SELECT statement requested the names of all the tables stored in the column TABLE_NAME to be returned from the system table named USER_TABLES.
The previous two examples made use of tables stored in the system tablespace. Similar commands can be issued for tables in user tablespace or any other tablespace that you have access to.
The following examples assume that the table CAR_VINFO and its contents exist:
SQL> select * from car_vinfo;
CAR_NAME CAR_TYPE CAR
------------------------------ --------------- ---------------
Eagle Talon sport Chrysler
GMC Jimmy 4x4 utility General Motors
Sentra sedan Nissan
Pathfinder 4x4 utility Nissan
Conquest 4x4 truck Dodge
Fiero GM
Supra Turbo sport Toyota
SQL>
Example of retrieving all rows of information from CAR_VINFO table.
The next example extracts the names of all the cars and nothing else. It tells Oracle to produce a listing of all the items in the column CAR_NAME from the table CAR_VINFO:
SQL> select car_name from car_vinfo;
CAR_NAME
------------------------------
Eagle Talon
GMC Jimmy
Sentra
Pathfinder
Conquest
Fiero
Supra Turbo
SQL>
Example of extracting one column of information from CAR_VINFO table.
It should be apparent that all the information from a table may be retrieved by using the '*' character and that only specific columns will be retrieved if their names are explicitly specified.
The next example is similar to the above example except that it will only select the 4x4 utility vehicles from the same table. It uses the WHERE clause to restrict the data (rows) that are returned.
SQL> select car_name from car_vinfo where car_type = '4x4 utility';
CAR_NAME
------------------------------
GMC Jimmy
Pathfinder
Example of extracting information according to CAR_TYPE.
The following two examples demonstrate the use of multiple search criteria. That is, example 1 makes use of the logical operator OR but the 2nd example makes use of a special operator, IN, that is intended for use with the WHERE clause. Both examples return the exact same results:
SQL> select car_name from car_vinfo where (1)
2 car_type = 'sport' or car_type ='sedan';
CAR_NAME
------------------------------
Eagle Talon
Sentra
Supra Turbo
SQL> select car_name from car_vinfo where (2)
car_type in ('sport', 'sedan');
CAR_NAME
------------------------------
Eagle Talon
Sentra
Supra Turbo
SQL>
Example of using a logical OR and the IN operator -- both return the same results.
This last example also restricts the data returned by using the WHERE clause, but it also shows how to use the LIKE and % operators to perform fuzzy searches:
SQL> select car_name, car_type from car_vinfo where car_type like '%4x4%';
CAR_NAME CAR_TYPE
------------------------------ ---------------
GMC Jimmy 4x4 utility
Pathfinder 4x4 utility
Conquest 4x4 truck
SQL>
Example of using the LIKE and % operators to do fuzzy searches.
Notice that 2 columns are returned, as was specified in the first part of the select statement. The wild-card specified in the WHERE clause refers to any string containing the sub-string "4x4". The '%' characters mean that other text could have appeared before or after of the sub-string we specified.
Following are some common operators that can be used with the WHERE clause:
Operator Purpose Operator Purpose
= equality test != test for
inequality (not
equal to)
^= same as != <> same as !=
< less than > greater than
<= less than or >= greater than or
equal to equal to
in equal to any not in not equal to any
member in member in
parentheses parentheses
between A and B greater than or not between A and not greater than
equal to A and B or equal to A and
less than or not less than or
equal to B equal to B
like '%sub%' contains the
given text
Logical operators can also be used with the WHERE clause:
OR returns TRUE when either one of the conditions is true AND returns TRUE only when both conditions are true
So far all the operations have been done on just one table at a time. In reality, queries to relational database most often require references to 2 or more tables at a time. This is called a join in relational database jargon.
Simply put, a join combines the columns of one table with the columns of another. This is why it is so important to have nicely defined tables and unique primary keys that can be used to combine the rows of information.
When joining tables it is very important to note the order in which they are joined and the conditions that are used. It is very easy to extract the wrong information simply because table or column names are in the improper order.
The next few examples demonstrate how to peform joins using the following 2 tables:
SQL> select * from car_vinfo;
CAR_NAME CAR_TYPE CAR
------------------------------ --------------- ---------------
Eagle Talon sport Chrysler
GMC Jimmy 4x4 utility General Motors
Sentra sedan Nissan
Pathfinder 4x4 utility Nissan
Conquest 4x4 truck Dodge
Fiero GM
Supra Turbo sport Toyota
SQL> select * from car_nvinfo;
CAR_NAME CAR_CYL CAR_LP100
------------------------------ ---------- ----------
Supra Turbo 4 10.1
Eagle Talon 4 7.4
GMC Jimmy 6 10.9
Sentra 4 4.8
Pathfinder 6 11.3
Contents of the CAR_VINFO and CAR_NVINFO tables.
This example will retrieve information about cars that have 5 or more cylinders and belong to General Motors:
SQL> select car_vinfo.car_name, car_type
2 from car_vinfo, car_nvinfo
3 where car_nvinfo.car_cyl >= 5
4 and car_vinfo.car = 'General Motors';
CAR_NAME CAR_TYPE
------------------------------ ---------------
GMC Jimmy 4x4 utility
GMC Jimmy 4x4 utility
SQL>
GM vehicles with 5 or more cylinders -- notice that information is returned twice.
Notice that the same information is returned twice. This is a common occurance in SQL programming and is fixed by making sure the primary keys of each table are equal to each other. Some versions of SQL have non-standard commands to elliminate these duplicates, but a brute-force (and portable) method is like the following:
SQL> select car_vinfo.car_name, car_type
2 from car_vinfo, car_nvinfo
3 where car_nvinfo.car_cyl >= 5
4 and car_vinfo.car = 'General Motors'
5 and car_vinfo.car_name = car_nvinfo.car_name;
CAR_NAME CAR_TYPE
------------------------------ ---------------
GMC Jimmy 4x4 utility
SQL>
Modified SELECT statement to elliminate duplicate information.
The last condition of the WHERE clause ensures that the primary keys for both tables are identical.
You should notice that a column can be explicity selected by specifying it along with the table from which it is from. This is very important if you have columns with the same name in different tables. For example, to specify the column CAR in the table CAR_VINFO:
car_vinfo.car
The next example is similar to the previous. It selects 2 columns of information from 2 different tables. The selection criteria is based on the gas consumption of the vehicle:
SQL> select car_type, car_cyl from car_vinfo, car_nvinfo
2 where car_lp100 between 5 and 11
3 and car_vinfo.car_name = car_nvinfo.car_name;
CAR_TYPE CAR_CYL
--------------- ----------
sport 4
sport 4
4x4 utility 6
SQL>
Important Note:
If a column only appears in one table then it is not necessary to explicitly name the table when specifying the column in a SELECT. Some programmers, however, prefer to explicity specify everything.