≡ 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

How Do I combine results from more than one table?

Combine data in SQL

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.

What are the ways I can combine results from more than one query?

Many times in a relational database the information you want to show in your query is in more than one table.  This begs the question “How do you combine results from more than one table?”

SQL wouldn’t be a very useful language if it didn’t provide an easy means to do so.  Fortunately there are three main ways you can combine data from multiple tables.  We’ll go over these briefly here and provide links to more in-depth articles.

Three Main Ways to Combine Data

Data in relational database tables are organized into rows and columns.  As we investigate ways to combine data, keep in mind that the end result will be to either add more columns to a result, perhaps from another relate table, or rows, by taking a set of rows from two or more tables.

When most people learn to combine data they lean about:

  1. JOIN – You can use joins to combine columns from one or more queries into one result.
  2. UNION – Use Unions and other set operators to combine rows from one or more queries into one result.
  3. Sub Queries – Sometimes called nested queries, these can be used to perform a separate search in the database showed results can be used in another query.

[click to continue…]

0 comments

How Do I Use SQL Server to Sort Alphanumeric Values?

Dutch Alphabet

Sort Alphanumeric Values with SQL Server

Recently a reader asked me how to sort an alpha numeric field in SQL in natural order.

When we have a numeric fields we expect values to be sorted in ordinal order, for example: 1,2,5,7,10,11,15,20,21

However when these numbers are in character field, such as char or varchar, the sort becomes  alphabetic and the ordering not what we may wish:  1,10,11,15,2,20,21,5,7.

For numerals in a text field we could easily convert the values to numeric values and sort; however, this option isn’t available if the column values contain alphanumeric values.

NOTE:  This is somewhat of an intermediate to advanced article.  I’m assuming that you already know of the LEFT, SUBSTRING, and CONVERT functions.  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. [click to continue…]

0 comments
SQL Except Operator

How to use the Except Operator

 

The EXCEPT operator is used to exclude like rows that are found in one query but not another.  It returns rows that are unique to one result.  To use the EXCEPT operator, both queries must return the same number of columns and those columns must be of compatible data types.

Visual Example of Except

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. [click to continue…]

0 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. [click to continue…]

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