SQL - Select

SQL SELECT may be the most commonly used command by SQL programmers. It is used to extract data from databases and to present data in a user-friendly table called the result set.

SQL Select Query Template:

SELECT table_column1, table_column2, table_column3 
FROM my_table;

Select queries require two essential parts. The first part is the "WHAT", which determines what we want SQL to go and fetch. The second part of any SELECT command is the "FROM WHERE". It identifies where to fetch the data from, which may be from a SQL table, a SQL view, or some other SQL data object.
Now we would like SQL to go and fetch some data for us from the orders table that was created in the previous lesson. How do we translate this request into SQL code so that the database application does all the work for us? Simple! We just need to tell SQL what we want to select and from where to select the data, by following the schema outlined below.

SQL Select Query Code:

USE mydatabase;

SELECT id, customer, day_of_order, product, quantity
FROM orders;

SQL Orders Table Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Pen	4

Below, we will manipulate the result output by rearranging the list of table column names inside of the SELECT statement.

SQL Select Query: Rearranged:

USE mydatabase;

SELECT day_of_order, customer, product, quantity
FROM orders;

SQL Orders Table Results:

day_of_order	customer	product	quantity
2008-08-01 00:00:00.000	Tizag	Pen	4

By rearranging the table column list inside the SELECT statement, we altered the appearance of the result set. Also, by not including the id column in the list of table columns, SQL did not fetch any column data for this column because we didn't ask SQL to do so.

SQL - Select All (*)

"SELECT (*)" is a shortcut that can be used to select all table columns rather than listing each of them by name. Unfortunately, going this route doesn't allow for you to alter the presentation of the results.

SQL Select All Query:

USE mydatabase;

SELECT *
FROM orders;

SQL Orders Table Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Pen	4

SQL - Selecting Data

The (*) query statement should be used with caution. Using this against our little tutorial database will surely do no harm, but using this query against an extremely large database may not be the best practice. Large databases may have web services or applications attached to them, so frequently updating and accessing large quantities data may temporarily lock a table for fractions of a second or more. If this disruption happens to occur just as some piece of data is being updated, you may experience data corruption.
Taking every precaution to avoid data corruption is in your best interest as a new SQL programmer. Corrupted data may be lost and never recovered, and it can lead to even more corruption inside a database. The best habits are to be as precise as possible, and in the case of select statements, this often means selecting minimal amounts of data when possible.
At this point, you should feel comfortable with SELECT and how to look into your database and see actual data rows residing inside of tables. This knowledge will prove invaluable as your SQL skills develop beyond the basics and as you begin to tackle larger, more advanced SQL projects.

Posted in: SQL