Quering data

The SELECT clause

A SELECT statement retrieves zero or more rows from one or more database tables or database views. ^[1]

The Select object builds a SELECT statement that can be executed in a database by using a SelectStatement instance. The SelectStatement only retrieves data from the database and has no persistent effects on the database.

Some essential Select methods:

• Select::columns() - sets the list of columns to include in the final result
• Select::from() - indicates the table(s)
• Select::where() - restricts the rows returned
• Select::groupBy() - projects rows having common values into a smaller set of rows
• Select::having() - filters rows resulting from the Select::groupBy() call
• Select::orderBy() - sorts the resulting data
• Select::createStatement() - creates the SelectStatement object

Common Table Expressions (CTE)

The WITH clause was introduced in standard SQL to simplify complex queries, especially those with JOINs and subqueries. A WITH clause defines a temporary data set whose output is available to be referenced in subsequent queries. ^[2]

CTEs, like database views and derived tables, enable users to more easily write and maintain complex queries via increased readability and simplification. This reduction in complexity is achieved by deconstructing ordinarily complex queries into simple blocks to be used, and reused if necessary, in rewriting the query. ^[3]

The With object (Common Table Expressions)

You can specify common table expressions using The With object that has one or more Select subclauses.