The Data School - SQL Clauses

Previously I wrote a bl0g on the main operation order for SQL. This was a walkthrough of the basic operations needed. This blog will be looking more at clauses that may help with more advanced problems.

Quick recap of the operations order ( as we are going to need them for the new examples).

The new clauses that we are looking at:

IN
AND
OR
BETWEEN
LIKE
TOP and LIMIT
COUNT
IS NULL
IS NOT NULL
JOINS:

Inner
Left
Right
Full Outer

Below shows you what each of these are with an example:

WARNING: There are times where you may need to use multiple of the above operations to get to the answer you will see this within some of the examples below.

IN- All0ws you to specify multiple values in a WHERE clause

Example: Return a unique list of cities in Alabama, Georgia and Florida

Select distinct = unique. Because we are interested in these three states this IN function means we can just include all within brackets rather then writing WHERE "State" = 'Alabama' AND "State" = 'Georgia' AND "State" = 'Florida. As we can see from this we get 55 rows as a result.

TIP: " "= field name and ' '= something within the data like the particular state as shown above.

AND- displays a record if both the first condition AND second condition are true.

Example: How many records have a quantity between 10 and 100 and sales greater than 1,000?

This statement uses multiple queries as it uses BETWEEN as well as an AND. As we can see the number of rows for this is 30.

OR- displays a record if either the first condition OR second condition are true.

Example: Return all records in the Central region or in the Home Office customer segment.

For this we can see that there are 3,668 rows for this query which are either in central region OR home office (segment).

BETWEEN- This is used to select values within a range in a WHERE clause or HAVING clause.

Example: Get all records that have a quantity between 5 and 9

When we consider the results we can see that all the quantity is between 5 and 9

LIKE- This function uses wildcards. The wildcards are used to search for data within a table.

Example: Can I get a Unique list of products that start with the letter ‘B’?

We can see from the above that we would use SELECT DISTINCT as we want unique values, and in the results pane we get a total of 117 unique fields starting with the letter B.

TOP /LIMIT is used to specify the number of records to return.

Example: Return the first 23 records from the Superstore table

We can see both examples bring back the same thing its just two different ways to write it.

COUNT- returns the number of rows that matches a specified Criteria. NULL values will not be counted.

Example: How many records have a quantity between 10 and 100 and sales greater than 1,000?

So As we saw before the row count was 30:

But using the count function it will only give us 1 row which is the number counted

IS NULL - identifies null records

Example: In the Superstore_Joined table, how many records do not have a Return ID? List in descending order by number of records per region.

This shows the number of nulls in each region.

IS NOT NULL - identifies records that is not null.

Example: In the Superstore_Joined table, how many records do have a Return ID? List in descending order by number of records per region.

JOINS: inner, left, right and full outer

Example INNER JOIN: What is the totals sales per person? Sort by sales descending
and Round Sales to the nearest whole number
HINT: Use Superstore_Orders and People

When joining you must use Aliases so that they know which table the data is coming from for this one I used T1 and T2.

Example LEFT JOIN: For each Region, return the totals sales and the number of orders returned. Sort by the highest number of orders returned join using orders and returns.

Using same example as above with RIGHT JOIN:

The right join does not have as many ID's as the left join therefore the sales is significantly lower.

Same example again for FULL JOIN:

Using the FULL OUTER JOIN we can see the total sales is the sales from the previous left join added to the right join. We can now also see the number of Order IDs in the returns table and the order tables.