Chapter 3 Flashcards
CRUD
Create, Read, Update, Delete
Inner Join
The inner join connects rows in different tables together based on a condition known as the join predicate. The join predicate is a logical condition of equality. Each row in the first table is compared against every other row in the second table. For row combinations that meet the inner join predicate, that row is returned in the query. Otherwise, the row combination is discarded.
Left Outer Join (Left Join)
Left outer joins are where the left table (that is, the table mentioned first in a join clause) will have every row returned. If a row from the other table is not found, a row of NULL is returned. Left outer joins are performed by using the LEFT OUTER JOIN keywords followed by a join predicate. This can also be written in short as LEFT JOIN.
Right Outer Join (Right Join)
A right outer join is very similar to a left join, except the table on the “right” (the second listed table) will now have every row show up, and the “left” table will have NULLs if the join condition is not met.
FULL OUTER JOIN
Finally, there is the full outer join. The full outer join will return all rows from the left and right tables, regardless of whether the join predicate is matched. For rows where the join predicate is met, the two rows are combined in a group. For rows where they are not met, the row has NULL filled in. The full outer join is invoked by using the FULL OUTER JOIN clause, followed by a join predicate.
CROSS JOIN
The cross join is mathematically what is also referred to as the Cartesian product – it returns every possible combination of rows from the “left” table and the “right” table. It can be invoked using a CROSS JOIN clause, followed by the name of the other table.
UNION
Putting multiple queries together vertically; that is, by keeping the same number of columns but adding multiple rows
CASE WHEN
A function that allows a query to map various values in a column to other values. CASE WHEN, WHEN, ELSE
COALESCE
Useful technique is to replace NULL values with a standard value
NULLIF
Opposite of COALESCE. A two-value function and will return NULL if the first value equals the second value…
LEAST/GREATEST
Each function takes any number of values and returns the least or the greatest of the values, respectively.
Casting (column::datatype - what you want to change the column to)
Another useful data transformation is to change the data type of a column within a query. This is usually done to use a function only available to one data type, such as text, while working with a column that is in a different data type, such as a numeric.
DISTINCT
Determines the unique values in a column or group of columns
DISTINCT ON -
Allows you to ensure that only one row is returned where one or more columns are always unique in the set.
