Skip to content

introduction.md

Latest commit

 

History

History
142 lines (106 loc) · 6.38 KB

introduction.md

File metadata and controls

142 lines (106 loc) · 6.38 KB

The SELECT statement

In SQL, a SELECT statement allows you to retrieve data from a database. The result of a SELECT statement is a result set -- some number of values arranged in rows and columns, where each column is a particular attribute of the data and each row is a set of values for each column.

Roughly, you can think of the set of columns as a data structure, where each column represents a field of the data structure, and each row represents a concrete instance of the data structure.

With a SELECT statement, you can specify the data you want and optionally transform, filter, and/or modify the shape of the output data.

The basics

The anatomy of a basic SELECT statement is as follows:

SELECT <columns>
FROM <source>
WHERE <criteria>;

Note that the line breaks are not required; any spacing will suffice to separate the different clauses (i.e. parts) of the SELECT statement.

Immediately following the SELECT keyword is a list of the columns that you want in the result. The FROM clause identifies the source of the data, which is typically a table in the database. The WHERE clause filters the output data by one or more criteria.

For example, consider a database with a table named inventory containing the following data:

upc category supplier brand product_name weight stock
812345670019 Cookware KitchenCo HearthStone 10" Non-Stick Skillet 1.8 42
845678120334 Utensils HomePro Supply PrepMaster Stainless Steel Ladle 0.4 120
899001234556 Appliances Culinary Depot QuickBrew Single-Serve Coffee Maker 4.2 18
823450987112 Cookware KitchenCo FreshKeep 12-Piece Glass Container Set 6 33
867530900221 Utensils HomePro Supply EdgeCraft 8' Chef's Knife 0.6 27
880012349876 Appliances Culinary Depot HeatWave Compact Toaster Oven 7.5 14
833221109443 Utensils HomePro Supply PureScrub Heavy-Duty Kitchen Sponge (3-pk) 0.2 200
899998877665 Cookware KitchenCo IronCraft Cast-Iron Grill Pan 5.3 21
844110220987 Appliances Culinary Depot BlendPro High-Speed Personal Blender 3.1 16

If we want to simply retrieve all of the data from the table, we could run the following query:

SELECT * FROM inventory;

Result:

upc           category    supplier        brand        product_name                      weight  stock
------------  ----------  --------------  -----------  --------------------------------  ------  -----
812345670019  Cookware    KitchenCo       HearthStone  10" Non-Stick Skillet             1.8     42   
845678120334  Utensils    HomePro Supply  PrepMaster   Stainless Steel Ladle             0.4     120  
899001234556  Appliances  Culinary Depot  QuickBrew    Single-Serve Coffee Maker         4.2     18   
823450987112  Cookware    KitchenCo       FreshKeep    12-Piece Glass Container Set      6       33   
867530900221  Utensils    HomePro Supply  EdgeCraft    8' Chef's Knife                   0.6     27   
880012349876  Appliances  Culinary Depot  HeatWave     Compact Toaster Oven              7.5     14   
833221109443  Utensils    HomePro Supply  PureScrub    Heavy-Duty Kitchen Sponge (3-pk)  0.2     200  
899998877665  Cookware    KitchenCo       IronCraft    Cast-Iron Grill Pan               5.3     21   
844110220987  Appliances  Culinary Depot  BlendPro     High-Speed Personal Blender       3.1     16

But if we only want the category and product_name values, we could specify those columns:

SELECT category, product_name FROM inventory;

Result:

category    product_name                    
----------  --------------------------------
Cookware    10" Non-Stick Skillet           
Utensils    Stainless Steel Ladle           
Appliances  Single-Serve Coffee Maker       
Cookware    12-Piece Glass Container Set    
Utensils    8' Chef's Knife                 
Appliances  Compact Toaster Oven            
Utensils    Heavy-Duty Kitchen Sponge (3-pk)
Cookware    Cast-Iron Grill Pan             
Appliances  High-Speed Personal Blender     

The `FROM` clause is optional.
A statement like `SELECT "Hello, world.";` is perfectly valid, and will generate the following result:

```
"Hello, world."
---------------
Hello, world. 
```

Filtering data with the WHERE clause

The WHERE clause allows you to filter the data retrieved by a SELECT statement. For example, if we only want Appliances:

SELECT * FROM inventory
WHERE category = "Appliances";

Result:

upc           category    supplier        brand      product_name                 weight  stock
------------  ----------  --------------  ---------  ---------------------------  ------  -----
899001234556  Appliances  Culinary Depot  QuickBrew  Single-Serve Coffee Maker    4.2     18   
880012349876  Appliances  Culinary Depot  HeatWave   Compact Toaster Oven         7.5     14   
844110220987  Appliances  Culinary Depot  BlendPro   High-Speed Personal Blender  3.1     16

Or maybe we only want data where the weight is between 2.0 and 6.0:

SELECT * FROM inventory
WHERE weight BETWEEN 2.0 AND 6.0;

Result:

upc           category    supplier        brand      product_name                  weight  stock
------------  ----------  --------------  ---------  ----------------------------  ------  -----
899001234556  Appliances  Culinary Depot  QuickBrew  Single-Serve Coffee Maker     4.2     18   
823450987112  Cookware    KitchenCo       FreshKeep  12-Piece Glass Container Set  6       33   
899998877665  Cookware    KitchenCo       IronCraft  Cast-Iron Grill Pan           5.3     21   
844110220987  Appliances  Culinary Depot  BlendPro   High-Speed Personal Blender   3.1     16

In addition to = and BETWEEN...AND, the WHERE clause supports a wide range of expressions, including comparison (<, <=, >, >=), pattern matching (LIKE, GLOB, REGEXP, MATCH), and checking for membership in a list (IN, NOT IN). See SQL Language Expressions for the complete documentation.