> Joins exist only for the duration of the query. They are not part of the database.

> Joined columns don’t need the same column name (except when doing a natural join).

> If a joining column contains a null, the null never joins.

> The joining columns must be compatible, such as (INTEGER, NUMERIC, FLOAT), (CHAR, VARCHAR), (DATE, TIME, TIMESTAMP).

There are two ways to do a join. You can use a JOIN or a WHERE syntax. WHERE joins were the only way to do a join until SQL-92 introduced the JOIN syntax. As a result, you will find both forms in use.

                  JOIN syntax format:                                         Example:

          SELECT select_columns                                   SELECT V.VNDR_ID,
            FROM table1 join_type table2                                     V.VENDOR_NAME,
                ON join_condition                                                   I.DESCRIPTION
        [WHERE search_condition]                                   FROM VENDOR_TBL V
         [GROUP BY grouping_columns]                           INNER JOIN INVENTORY_TBL I
        [HAVING search_condition]                                        ON V.VNDR_ID = I.VNDR_ID;
        [ORDER BY sort_columns];

 Select_columns is one or more selected columns, separated by a comma, from table1 and table2.

Join_type is the type of join to be performed, such as INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, NATURAL JOIN, and CROSS JOIN.

ON is not allowed for CROSS JOIN or NATURAL JOIN.

Join_condition shows the conditions for one or more joined rows.
The format is [table1.]column oper [table2.]column where oper is =, <, <=, >, >=, <>, AND, OR, or NOT.

The WHERE, GROUP BY, HAVING, and ORDER BY have been discussed in previous topics.

> WHERE syntax joins can only be done with AND. WHERE syntax joins using OR are not legal.

You can create table aliases just as you did column aliases. The difference is that with column aliases, the key word AS is required and it changes the resulting column name. With the table alias, the key word AS is optional and the table alias is used only to associate columns with tables — the resulting table is unchanged.In a FROM or JOIN clause, format: table_name [AS] alias_name

Alias is a single word, without quotes, and contains only letters, digits, or underscores. You can not use key words, spaces, punctuation, or special characters.

> Each table’s alias name must be unique within the SQL statement.> Besides being used to associate specific column names with tables, table aliases are used to reduce statement clutter which makes the statement more easy to understood.

> A table alias does not change the table name in the table definition and it exists only for the duration of the statement.

> An alias name negates the table name for the duration of the statement. Therefore, all references to the table must use the alias name in all qualifying references.

> Alias names can also be used for table views.

As mentioned earlier, column names must be unique within each table. However, the same column name can be used in other tables. To uniquely identify names that are the same in multiple tables, we use a qualifying name.A qualified name is a table name followed by a period and the name of the column in the table.

                       Format:                         table_name.column_name

Tables must have unique names within a database. Therefore, a qualified name is unique within the database.

These two statements are functionally identical:

                    SELECT VNDR_ID,                          SELECT VENDOR_TBL.VNDR_ID
                                 VENDOR_NAME                              VENDOR_TBL.VENDOR_NAME
                       FROM VENDOR_TBL;                      FROM VENDOR_TBL;

> A column name does not need to be qualified if it is a unique name among all the column names of all the tables
used in a statement.> Qualified and unqualified names may be intermixed in a statement.

> It is a good idea to use qualified names. Then, if a matching name is added to another table in the future,
there will not be a problem with an unqualified name.

> Some RDBMS systems might require a hierarchy of qualification such as server, database, schema, owner, table.

> SQL Server requires server.database.owner.table.

All of the queries up to this point have retrieved rows from a single table, but using a relational database means retrieving relational data from multiple tables.A JOIN is a table operation that uses related columns to combine rows from two or more input tables into one result table.

This is where the relational database (RDBMS) has its true power. There is so much more information that can be obtained from tables, through joins of related data, using the SELECT statement.

Most join statements can be rewritten as subselects and most subselect statements can be rewritten as joins.> Table columns used to join two or more tables do not have to be SELECTed, they just have to be in the JOIN or WHERE clause. However, these columns may appear in the SELECT statement if you wish to see them in the results table.

> Access, DB2, and SQL Server do not support NATURAL JOIN syntax. See NATURAL JOIN.

> MySQL only supports a NATURAL JOIN on an OUTER JOIN, not an INNER JOIN. It also uses the non-standard STRAIGHT_JOIN. See INNER JOIN and OUTER JOIN.

> Oracle 8i and earlier does not support the JOIN syntax. Only Oracle 9i and beyond support the JOIN syntax.
If JOIN is not supported, use the WHERE clause.

> PostgreSQL supports all JOINs.

Just as the WHERE clause limits the number of rows retrieved by the SELECT statement, the HAVING clause limits the number of groups retrieved for the GROUP BY clause.

                         SELECT      [ALL | DISTINCT]
                                           [* | column1[, column2, ...]]
                            FROM      [table1 correlation, ...
                                          | view1 correlation, ...]
                          WHERE       condition1[, condition2, ...]
                          GROUP BY  column1[, column2, ...]
                         HAVING      condition1[, condition2, ...]
                           ORDER BY column1 [ASC | DESC][, column2 [ASC | DESC], …];

> Unlike the WHERE clause, the HAVING clause can contain a column function.

> The HAVING clause comes after the GROUP BY clause and before the ORDER BY clause.

> The WHERE clause can eliminate data from consideration by the GROUP BY clause. The WHERE clause is applied before grouping happens. The HAVING clause is applied after grouping happens.

> An entry in the HAVING clause must match an entry in the SELECT statement or be a column function.
> Column names in the HAVING clause do not have to be in the same order as the SELECT statement.

> If the SELECTed name is qualified, the HAVING name must be qualified.

> An expression in the HAVING clause must match the exact same expression in the SELECT statement.

> Multiple HAVING conditions can be specified using AND, OR, and NOT.

The GROUP BY clause divides a table into logical groups.
The order of multiple columns in the GROUP BY clause specifies the grouping level from the highest to the lowest level of grouping.
Only one row results for each distinct value in the grouping values.
Each row in the result contains summary data of the values in the grouping columns.

                                  SELECT    [ALL | DISTINCT]
                                                  [* | column1[, column2, ...]]
                                    FROM     [table1 correlation, ...
                                                | view1 correlation, ...]
                                   WHERE     condition1[, condition2, ...]
                                  GROUP BY column1[, column2, ...]
                                 HAVING     condition1[, condition2, ...]
                                  ORDER BY column1 [ASC | DESC][, column2 ...];

> The GROUP BY clause is not necessary unless used with a column function.

> The GROUP BY clause comes after the WHERE clause and before the HAVING and ORDER BY clause.

> The WHERE clause can eliminate data from consideration by the GROUP BY clause.

> A column name in the GROUP BY clause must match a column name in the SELECT statement. Column names in the GROUP BY clause do not have to be in the same order as the SELECT statement. If the SELECTed name is qualified, the GROUP BY name must be qualified.

> An expression in the GROUP BY clause must match the exact same expression in the SELECT statement.

> If a grouping column contains a NULL or NULLs, the NULLs become a group.

> To sort the GROUP BY groups, use the ORDER BY clause.

> Without the ORDER BY clause, the groups returned by the GROUP BY clause can be in any order. In some RDBMS Systems, GROUP BY implies ORDER BY.

> Multiple grouping columns in the GROUP BY clause creates nested groups.

> Table AS aliases are allowed as qualifiers, but AS aliases can’t be used in the GROUP BY clause.
See Table Aliases (AS).

> If you use a column for GROUP BY frequently, that column should have an index.

> If used without a column function, GROUP BY is the same as DISTINCT.

> RDBMS systems such a MySQL and PostgreSQL allow column aliases in the GROUP BY clause.

Column function expressions can be used in the SELECT clause to perform calculations. We have a table: EMPLOYEE_TBL

If we wanted to know the difference between the minimum and maximum RATE_HOUR, we could do the following:

                    SELECT (MAX(RATE_HOUR) - MIN(RATE_HOUR))
                      FROM EMPLOYEE_TBL;

Result Table:

                   

Calculations are evaluated in the following order:
1. Expressions within parentheses with the innermost first.
2. Multiplication and division.
3. Addition and subtraction.
4. Left to right.

Column functions can be SELECTed with other column functions because they each return only one value.
Column functions can not be combined with SELECTed columns or scalar functions because, they return multiple rows.

If we have a table: INVENTORY_TBL

          SELECT MIN(PEOPLE)      AS MIN,
                       MAX(PEOPLE)     AS MAX,
                       AVG(PEOPLE)      AS AVG,
                       COUNT(PEOPLE) AS NOT_NULL,
                       COUNT(*)           AS ALL
             FROM INVENTORY_TBL;

Result Table:

 

COUNT(expression) counts the number of rows selected that are not null.
COUNT(*) counts all of the rows, including those that are null.
The expression is a column name, numeric expression, or literal.
Nulls are included in the count with COUNT(*), otherwise Nulls are removed from the count.
The argument values can be character, numeric, datetime, or asterisk (*).
The result will be a numeric integer of zero value or greater.
The keyword, DISTINCT, can be used, except when using COUNT(*).

 

          COUNT(expression)                     <- Count the number of non-nulls.

                              or

          COUNT(DISTINCT column-name) <- Count the number of unique non-nulls.

                              or

          COUNT(*)                                      <- Count the number of null and non-nulls.

 

Notes:
COUNT(*) - COUNT(expression)                        <- Gives the number of nulls in a column.
((COUNT(*)-COUNT(expr))*100)/COUNT(*)        <- Gives the percentage of nulls.