SELECT Query

A SELECT query is a type of SQL (Structured Query Language) statement used to retrieve data from a database. It allows you to specify which columns from a table you want to retrieve, and optionally, to apply filters to limit the rows that are returned based on certain criteria.

The basic syntax of a SELECT statement is:

SQLSELECT column1, column2, ... 
FROM table_name
WHERE condition;

In this syntax, column1, column2, etc. are the columns you want to retrieve data from, and table_name is the name of the table(s) from which you want to retrieve data.

The WHERE clause is optional, but it allows you to specify a condition that filters the results based on certain criteria. For example, if you only want to retrieve data where a certain column's value is greater than a specific number, you can use the WHERE clause to specify that condition.

When you execute a select query, the database system returns a result set, which is a table containing the data that matches the specified criteria. This result set can then be used for further processing or analysis, depending on your needs.

The SELECT query with * retrieves all columns from a table. Here's an example:

SQLSQL> select * from countries;

CO COUNTRY_NAME              REGION_ID
-- ------------------------ ----------
AR Argentina                         2
AU Australia                         3
BE Belgium                           1
BR Brazil                            2
CA Canada                            2
CH Switzerland                       1
CN China                             3
DE Germany                           1
DK Denmark                           1
EG Egypt                             4
FR France                            1
IL Israel                            4
IN India                             3
IT Italy                             1
JP Japan                             3
KW Kuwait                            4
ML Malaysia                          3
MX Mexico                            2
NG Nigeria                           4
NL Netherlands                       1
SG Singapore                         3
UK United Kingdom                    1
US United States of America          2
ZM Zambia                            4
ZW Zimbabwe                          4

25 rows selected.

SQL>

This query will select all columns from the my_table table. The * symbol is a wildcard character that stands for "all columns".

Note that using * is convenient when you want to select all columns, but it's generally considered bad practice in production code. This is because if the table schema changes and new columns are added, your query could start returning unexpected results.

Where

The SELECT query with a WHERE condition is used to retrieve rows from a table that meet a specific condition. Here's an example:

SQLSELECT * FROM table_name WHERE condition;

In this example, the query will select all columns from the my_table table where the value of column1 is equal to the string 'value'. You can replace column1 and 'value' with the names of the specific column and value you're looking for.

Frequently asked questions

Why is SELECT * considered bad practice in production code?

It returns every column, including ones added to the table later, so the query can return unexpected results and more data than needed. Listing the columns you need keeps results predictable.

How do I select only some columns?

List them after SELECT, separated by commas: SELECT country_name, region_id FROM countries;

Is the WHERE condition case-sensitive?

SQL keywords are not, but string comparisons in Oracle are by default: WHERE country_name = 'india' will not match 'India'. Use UPPER(country_name) = 'INDIA' to ignore case.

In what order are WHERE and ORDER BY applied?

Rows are filtered by WHERE first, then the remaining rows are sorted by ORDER BY.