≡ Menu

Essential SQL

Want to Get Started Using SQL Server?

Databases Have Beautiful Structure

Now it is time to learn SQL in simple English.

  • Receive our free guide Getting Started with SQL Server!
  • Understand fundamental database concepts
  • Take your job to the next level.

Enter your email below to get our free guide to Getting Started with SQL Server.

Tiling_by_Patterns_stock

Introduction

One of the biggest issues beginning SQL writers have is being able to write queries that use more than one table.  In this series of articles we are going to show you how to write a query that combines, or joins, data from more than one table. Once you have gone through the examples you will understand how to write the basic commands to make this happen and why data is separated in the first place.

This first article introduces the concept of joining tables.  The focus is going to be more on the type of joins, not necessarily their syntax.  The later articles focus on the various types of joins.  Through the narrative and examples you’ll become very comfortable with each one.

In my prior articles you learned about the need to normalize to make it easier to maintain the data.  Though this makes it easier to maintain and update the data, it makes it very inconvenient to view and report information.  Typically the information you need to see has to be cross referenced across several tables for you to see the full picture. [click to continue…]

3 comments
Intersecting Rings

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

How to use the Intersect Operator

The INTERSECT operator is used to combine like rows from two queries.  It returns rows that are in common between both results.  To use the INTERSECT operator, both queries must return the same number of columns and those columns must be of compatible data types.

Visual Example of Intersect

In this example, the circles represent two queries.  The orange circle is the left query; whereas, the blue circle is the right.  The area within each circle represents that query’s results.

Intersect Operator

Visual Explanation of the Intersect Operator

As you can see the green portion represents the result of the INTERSECT operator.  This area represents those rows that are in both the left and right query.

Example

Below is the general format of the INTERSECT operator.

SELECT Name, BirthDate FROM Employee
UNION
SELECT Name, BirthDate FROM Customer

There are two queries which are separated by the INTERSECT operator.  The top query is commonly called the left query.

The query is valid since both the left and right queries contain the same number of columns and each column is a similar data type; Char and Date respectively.

Contrast this to

SELECT Name, BirthDate FROM Employee
UNION
SELECT Age, BirthDate, Name FROM Customer

Which is invalid on multiple levels.  First the number of columns isn’t the same.  Additionally, the data type for each column is incompatible.  For instance, Name, which is a Char column isn’t a compatible data type with Age.

Uses for Intersect

The intersect operator is good when you want to find common rows between two results.  The INTERSECT operator is similar to the AND operator; however, they operate on different database objects.

The Intersect operator is used to compare entire rows; whereas, the AND operator is used to compare columns within rows.

Say what?

Don’t worry, it becomes clearer below.

Intersect Two Tables

Let’s assume we want to find all job titles for positions held by both male and female employees.  How could we do this?  The first set is to compose the queries to find positions held by males, then to do the same for females.

Here is the query for males, the one for the females is very similar:

SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'M'

To finish we need to find out which titles are in common.  To do this we can use the INTERSECT operator.

SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'M'
INTERSECT
SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'F'

You may be tempted to try and simplify this statement by eliminating the INTERSECT operator all together and use the following

SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'M'
       AND Gender = 'F'

But this won’t simply work.  Why?  Because the Where clause is evaluated for each row and you’re never going to find a Gender value equal to both M and F for the same record.

Order By

To order the result by JobTitle we can use an ORDER BY clause.  Keep in mind this works on the the final row set returned by the interest operator.

SELECT   JobTitle
FROM     HumanResources.Employee
WHERE    Gender = 'M'
INTERSECT
SELECT   JobTitle
FROM     HumanResources.Employee
WHERE    Gender = 'F'
ORDER BY JobTitle

Equivalence

The INTERSECT hasn’t always been part of SQL Server .  Before its introduction to the language  you had to mimic the INTERSECT behavior using and INNER JOIN.

Below is the equivalent statement to find job titles in common for both genders:

SELECT DISTINCT M.JobTitle
FROM   HumanResources.Employee AS M
       INNER JOIN
       HumanResources.Employee AS F
       ON M.JobTitle = F.JobTitle
          AND M.Gender = 'M'
          AND F.Gender = 'F'

This join is called a self-join, since we are joining the table to itself.  The idea is to match up every JobTitle with same values.  By pairing these values together we can then compare their corresponding gender values and keep those where one gender is male and the other female.

NOTE:  These are equivalent to a point.  AS we have learned, NULL aren’t values, there fore NULL = NULL is always false.  Given this, the INNER JOIN will fail to match on joins; howver, the INTERSECT operator does match NULLS.

0 comments
Union Clause

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

Unions

In this lesson we are going to talk about the UNION clause.  You can use the UNION clause to combine rows from two different queries into one result.  Unlike a join, which combines columns from different tables, a union combines rows from different tables.  Here is an illustration of what an UNION looks like [click to continue…]

0 comments
Intersection, Exception, Union

Learn to use Union, Intersect, and Except Clauses

The UNION, INTERSECT, and EXCEPT clauses are used to combine or exclude like rows from two or more tables.  They are useful when you need to combine the results from separate queries into one single result.  They differ from a join in that entire rows are matched and, as a result, included or excluded from the combined result.

Overview

These operators can be used on any query; however, a couple simple of conditions must be met:

  1. The number and order columns must be the same in both queries
  2. The data types must be the same or compatible.

[click to continue…]

0 comments
OuterJoin

Outer Joins

The series starts with the article Introduction to Database Joins.  All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server. In this article we are going to cover outer joins.

An outer join is used to match rows from two tables.  Even if there is no match rows are included.  Rows from one of the tables are always included, for the other, when there are no matches, NULL values are included.

Types of Outer Joins

There are three types of outer joins:

  • Left Outer Join – All rows from the left table are included, unmatched rows from the right are replaced with NULL values.
  • Right Outer Join – All rows from the right table are included, unmatched rows from the left are replaced with NULL values.
  • Full Outer Join – All rows from both tables are included, NULL values fill unmatched rows.

Let’s dig a deeper and explore the left outer join. [click to continue…]

2 comments
Null and unknown values

What is a Null Value?

In databases a common issue is what value or placeholder do you use to represent a missing values.   In SQL, this is solved with null.  It is used to signify missing or unknown values.  The keyword NULL is used to indicate these values.  NULL really isn’t a specific value as much as it is an indicator.  Don’t think of NULL as similar to zero or blank, it isn’t the same.  Zero (0) and blanks “ “, are values.

In most of our beginning lessons we’ve assumed all tables contained data; however, SQL treats missing values differently.  It is important to understand how missing values are used and their effect on queries and calculations. [click to continue…]

0 comments