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
table

Resources

PostgreSQL SELECT
This tutorial shows you how to use the basic PostgreSQL SELECT statement to retrieve data from a single table.
SQL SELECT
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

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

PostgreSQL WHERE: Filtering Rows of a Query
In this tutorial, you’ll learn how to use PostgreSQL WHERE clause to filter rows returned from the SELECT statement.
SQL WHERE Clause
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

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 10

Resources

SQL SELECT TOP, LIMIT, FETCH FIRST ROWS ONLY, ROWNUM
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.
PostgreSQL LIMIT: Get a Subset of Rows Generated By a Query
This tutorial shows you how to use PostgreSQL LIMIT clause to get a subset of rows generated by a query.

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 name

Resources

PostgreSQL ORDER BY
This tutorial shows you how to use the PostgreSQL ORDER BY clause to sort result set returned by a query in ascending or descending order.
SQL ORDER BY Keyword
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

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 > 100

Resources

SQL AND, OR, NOT Operators
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.
PostgreSQL WHERE: Filtering Rows of a Query
In this tutorial, you’ll learn how to use PostgreSQL WHERE clause to filter rows returned from the SELECT statement.

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

SQL IN Operator
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.
PostgreSQL IN – Matching Against a List of Values
This tutorial shows you how to use the PostgreSQL IN operator to match against a list of values. It also shows you how to use the IN operator in a subquery.

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 100

Resources

SQL BETWEEN Operator
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.
PostgreSQL BETWEEN
This tutorial shows you how to use the PostgreSQL BETWEEN operator to check if a value is in a range of values.

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

PostgreSQL LIKE – Querying Data by Using Pattern Matching Techniques
This tutorial shows you how to use PostgreSQL LIKE and ILIKE operator to query data by using pattern matching techniques.
SQL LIKE Operator
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

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 table

Resources

SQL CASE Statement
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.
PostgreSQL CASE
In this tutorial, we will show you how to use the PostgreSQL CASE expression to form conditional query.

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:

SQL Order of Operations
Is the order in which SQL operations are executed important? Improve your SQL skills by learning the order of operations execution in SQL.