Common Syntax: SQL

This procedure provides an overview of common syntax, including the SELECT, FROM, WHERE and ORDERBY statements.

SELECT Section

The SELECT section specifies the data table and columns you want to retrieve. Use commas to separate multiple data references. For example:

SELECT debt.debt_id,
       demographic.first_name as "First", -- use aliases to make the column names more readable 
       demographic.last_name as "Last",
       debt.current_balance / 100.0 as "currentBalance" -- balances are stored in cents, so divide by 100 to get dollars

FROM Section

The FROM section lists the table(s) you are referencing, separated by commas. For example:

FROM debt,
     demographic

Important Note

Joining tables in the FROM section without additional clauses can result in a cross join. This combines every row from one table with every row from another table, potentially leading to a large and overwhelming result set.

To avoid unintended consequences, it's crucial to use additional clauses like the WHERE clause. These clauses define the relationship between tables and filter the result set based on specific conditions. By specifying criteria, you can narrow down the output to relevant and desired data.

It's important to note that mastering table joins requires further learning and practice. Beginners should consult beginner-friendly tutorials or resources for a better understanding of table joins and their proper usage.

WHERE Section

The WHERE section defines criteria to filter rows in the specified table. Without a WHERE statement, the query returns all rows in the table.

The example below demonstrates the usage of multiple conditions in a WHERE clause to filter data from different tables in a SQL query. The conditions specify the relationships between the tables and the criteria that must be met for a row to be included in the result set. The code combines logical operators (AND) to link the conditions together and ensure that all conditions are satisfied for a row to be selected.

WHERE debt.debt_id = demographic.debt_id -- Multiple tables require a join condition
  AND demographic.demographic_type = 'PRIMARY'
  AND demographic.state = 'WA' -- additional filters

ORDERBY Section

ORDER BY demographic.last_name -- optionally sort the results

Full SQL Query

Assembling all parts, we get the complete SQL query:

SELECT debt.debt_id,
       demographic.first_name as "First", -- use aliases to make the column names more readable 
       demographic.last_name as "Last",
       debt.current_balance / 100.0 as "currentBalance" -- balances are stored in cents, so divide by 100 to get dollars
FROM debt,
     demographic
WHERE debt.debt_id = demographic.debt_id -- Multiple tables require a join condition
  AND demographic.demographic_type = 'PRIMARY'
  AND demographic.state = 'WA' -- additional filters
ORDER BY demographic.last_name -- optionally sort the results

Example SQL Result

Image Displays Full SQL Query Example

Note

It is essential to construct queries carefully to avoid system lock-ups or duplicate results.

Consider visiting PostgreSQL for comprehensive SQL documentation.