LU04 – SQL SELECT Statement basics with Joins

Contents

LU04 – SQL SELECT Statement basics with Joins

Learning Objectives

SQL Goals

Part 1: The SELECT with a Twist

Recall: the basic SELECT Statement

Your first twist: Column and table Aliasing

Column Formatting and Data type conversions

The DISTINCT Keyword

The TOP Keyword

Learning Objectives

Last week we were introduced to SQL and some of its basic commands. This week we’ll extend or SQL knowledge as we explore some additional SQL capabilities; specifically howfilter rows, format columns, and join tables. If you look at our methodology below you can see that we are still in the implementation phase of the SDLC. Upon completion of this learning unit you should be able to:

  • Describe and use various ways to join tables.
  • Learn to sort and filter SQL output.
  • Understand how to use special constructs on the SELECT statement.

SQL Goals

Our SQL goals for this learning unit will be to:

  1. Understand how to use the WHERE and ORDER BY clauses.
  2. Understand how to convert and format data using CAST and CONVERT.
  3. Use the AS keyword to alias column and table names.
  4. Learn about options for the SELECT statement, such as TOP and DISTINCT.

Part 1: The SELECT with a Twist

Recall: the basic SELECT Statement

The syntax diagram for the basic select statement is as follows:

SELECT {colname[, ..n] | * }
FROM tablename
[WHERE condition]
[ORDER BY col [DESC] [, ..n]

Some notes on the select statement:

  • After the SELECT keyword, you have a comma-separated list of column names from the table specified after the FROM keyword. These correspond to the column you will see in the select output.
  • The WHERE clause allows you to restrict or filter the rows, displaying only those where the provided conditionis true.
  • The ORDER BY clause is used to sort the output. More than one column may be specified to provide secondary sort orders (for example, sort by last name, and then first name).

Your first twist: Column and table Aliasing

Columns and tables can be aliased with the AS keyword. This allows you to resolve the ambiguity of column names which are the same in multiple tables, as well as provide a name for calculated columns. It is important to understand the alias does not create new column in the table, but merely applies a name to the output column.

Example:

Column Formatting and Data type conversions

There are two built-in functions you can use to change the data type or format a column of SQL output.

  • CAST( column AS datatype) format a column’s data.
  • CONVERT(datatype, column | expression) will convert a column or expression from one data type to another.

Example use of cast and convert:

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST (CONVERT) function to be specified. The chart below shows all explicit and implicit data type conversions allowed for SQL Server system-supplied data types, including bigint and sql_variant.

The DISTINCT Keyword

The DISTINCTkeyword allows you to restrict the output to only display the rows which are different from one another. If the typical output would display redundant rows, then DISTINCT will eliminate them from the output.

Example:

The TOP Keyword

This is a useful keyword that allows you produce a list of rows that occur at the high end or low end of a value range. For example you may need to figure out the highest paid employee, or the upper half of the highest paid employees. You would use TOP to produce these lists. There are two basic formats of the TOP clause:

TOP actually works more like FIRST. When you say select TOP 1 …. You really mean give me the first 1 row in the output.

Part 2: SQL Joins

The JOIN clause is used to combine the output of two or more separate tables. The result of a SELECT with a join is a “virtual table” representing the combined output from the two tables. Please note it is not a new table – only the output of the existing ones. The type of output you see depends on how you join the two tables.
For example, take the following tables:

The cross join (Cartesian product)

When you do not specify how the tables should be joined, you get a cross join also known as a Cartesian product. This lists all possible combinations of rows from both tables. Thus if one table has 1000 rows and another has 50, their cross join would have 50x1000 = 50,000. While there are some rare instances when this type of join is desired, it is typically done by mistake and not by design.
For example:

The inner join (Equijoin)

Typically, the type of join you want to use in your SQL statements will be an inner join or Equijoin. It is called an Equijoin because you’re typically combining the tables based on two columns (the PK and FK) where the share the same value. The results of an Equijoin “match up” the row in one table with its corresponding FK equivalent in another table, for example.

In the above example the Equijoin show us which player plays for which team.

Outer Joins

In the previous example, notice how the team (Stinkers) and player (Fudge) with no match for player_team_id=team_id was left out of the SQL output? Well sometimes we’d like to include these values in the output, and the way we do this is through the outer join. There are three types of outer joins:

  • Left outer join. Include the entire set of rows from the left table – those that do not match, display null from the columns of the right table.
  • Right outer join. Include the entire set of rows from the right table – those that do not match, display null from the columns of the left table.
  • Full outer join. A left and a right outer join at the same time.

For example to include all the teams (including those with no players):

Include all players, even those without a team:

Include all players, and teams, showing those who play on a team:

1 / 10