SQL Visual QuickStart Guide: Retrieving Data from a Table
- Retrieving Columns with SELECT and FROM
- Creating Column Aliases with AS
- Eliminating Duplicate Rows with DISTINCT
- Sorting Rows with ORDER BY
- Filtering Rows with WHERE
- Combining and Negating Conditions with AND, OR, and NOT
- Matching Patterns with LIKE
- Range Filtering with BETWEEN
- List Filtering with IN
- Testing for Nulls with IS NULL
This chapter is from the book
This chapter is from the book
This chapter is from the book
This chapter introduces SQL’s workhorse—the SELECT statement. Most SQL work involves retrieving and manipulating data by using this one (albeit complex) statement. SELECT retrieves rows, columns, and derived values from one or more tables in a database; its syntax is:
SELECT columns FROM tables [JOIN joins] [WHERE search_condition] [GROUP BY grouping_columns] [HAVING search_condition] [ORDER BY sort_columns];
SELECT, FROM, ORDER BY, and WHERE are covered in this chapter, GROUP BY and HAVING in Chapter 6, and JOIN in Chapter 7. By convention, I call only a SELECT statement a query because it returns a result set. DBMS documentation and other books might refer to any SQL statement as a query. Although SELECT is powerful, it’s not dangerous: You can’t use it to add, change, or delete data or database objects. (The dangerous stuff starts in Chapter 10.)
Retrieving Columns with SELECT and FROM
In its simplest form, a SELECT statement retrieves columns from a table; you can retrieve one column, multiple columns, or all columns. The SELECT clause lists the columns to display, and the FROM clause specifies the table from which to draw the columns.
To retrieve a column from a table:
Type:
SELECT column FROM table;
column is a column name, and table is the name of the table that contains column (Listing 4.1 and Figure 4.1).
Listing 4.1. List the cities in which the authors live. See Figure 4.1 for the result.
SELECT city FROM authors;
Figure 4.1 Result of Listing 4.1.
city ------------- Bronx Boulder San Francisco San Francisco New York Palo Alto Sarasota
To retrieve multiple columns from a table:
Type:
SELECT columns FROM table;
columns is two or more comma-separated column names, and table is the name of the table that contains columns (Listing 4.2 and Figure 4.2).
Listing 4.2. List each author’s first name, last name, city, and state. See Figure 4.2 for the result.
SELECT au_fname, au_lname, city, state FROM authors;
Figure 4.2 Result of Listing 4.2.
au_fname au_lname city state --------- ----------- ------------- ----- Sarah Buchman Bronx NY Wendy Heydemark Boulder CO Hallie Hull San Francisco CA Klee Hull San Francisco CA Christian Kells New York NY Kellsey Palo Alto CA Paddy O'Furniture Sarasota FLColumns are displayed in the same order in which they’re listed in columns, not the order in which they’re defined in table.
To retrieve all columns from a table:
Type:
SELECT * FROM table;
table is the name of a table (Listing 4.3 and Figure 4.3).
Listing 4.3. List all the columns in the table authors. See Figure 4.3 for the result.
SELECT * FROM authors;
Figure 4.3 Result of Listing 4.3.
au_id au_fname au_lname phone address city state zip ----- --------- ----------- ------------ -------------------- ------------- ----- ----- A01 Sarah Buchman 718-496-7223 75 West 205 St Bronx NY 10468 A02 Wendy Heydemark 303-986-7020 2922 Baseline Rd Boulder CO 80303 A03 Hallie Hull 415-549-4278 3800 Waldo Ave, #14F San Francisco CA 94123 A04 Klee Hull 415-549-4278 3800 Waldo Ave, #14F San Francisco CA 94123 A05 Christian Kells 212-771-4680 114 Horatio St New York NY 10014 A06 Kellsey 650-836-7128 390 Serra Mall Palo Alto CA 94305 A07 Paddy O'Furniture 941-925-0752 1442 Main St Sarasota FL 34236
Columns are displayed in the order in which they’re defined in table.