SQL Basics for Spatial SQL: Basic Query Structure
This post serves as the guide for the first video in the Learn Spatial SQL series, focusing on SQL Basics for Spatial SQL, specifically Basic Query Structure:
SELECT
SELECT provides the basic query structure to return data from a table. This is the most foundational query structure in SQL when returning data from a table.
Example
SELECT
*
FROM
tableResources
WHERE
WHERE allows you filter your data based on conditions in your data, such as find all rows where the column “state” has the name “NY”.
Example
SELECT
*
FROM table
WHERE state = 'NY'Resources
LIMIT/OFFSET
LIMIT is used to limit the number of rows returned from a query. For example if you limit the query using LIMIT 10, it will return the first 10 rows matching your query parameters. OFFSET will offset those results buy a specific number.
Example
SELECT
*
FROM table
LIMIT 10
OFFSET 10Resources
ORDER BY
ORDER BY allows you to order your query results by a column (or results returned from a function).
Example
SELECT
*
FROM table
ORDER BY nameResources
AND/OR
AND/OR allows for you to chain different conditional statements together after the WHERE clause.
Example
SELECT
*
FROM table
WHERE state = 'NY'
AND number > 100Resources
IN/NOT IN
IN allows you to create a WHERE clause but to select multiple values from a specific column.
Example
SELECT
*
FROM table
WHERE state IN ('NY', 'CA')Resources
BETWEEN
BETWEEN allows for you to select a range of data in the WHERE clause that falls between two values.
Example
SELECT
*
FROM table
WHERE number BETWEEN 0 AND 100Resources
LIKE
LIKE is used in a WHERE clause to search across string data to find values that match patterns.
Example
SELECT
*
FROM table
WHERE name LIKE 'New%'Resources
CASE/WHEN
CASE/WHEN provides a logical operation sort of like an if/else operation in other languages. Based on the statement, it will return new values based on the statement.
Example
SELECT
*,
CASE WHEN state = 'MN'
THEN 'Is Minnesota'
ELSE 'Not Minnesota' END AS is_minnesota
FROM tableResources
Order of operations
It is also important to note the order of operations in the query structure so you have each element in the right location. This is a great general tutorial to help get started:
