taking your sql beyond simple selects and (inner) joins

8/3/2019 Taking Your SQL Beyond Simple SELECTs and (Inner) Joins

1/48

Taking Your SQL Beyond Simple

SELECTs and (Inner) Joins

Charlie [email protected]


2/48

May 3, 2012 2

Why Are We Here?

Hopefully youve read the session description

Many developers quickly grasp how to do simple Selects and

simple (inner) joins in SQL But often the fail to get past those fundamentals

And end up missing data (for lack of understanding outer joins)

or writing code in client applications that would be better performed in thedatabase

Will introduce important fundamental features of SQL that will save you time and create more effective applications

You'll learn how to slice and dice data in many ways Handling Distinct Column Values

Manipulating Data with SQL Functions

Summarizing Data with SQL (Counts, Averages, etc.)

Grouping Data with SQL

Handling Nulls

And understand the value of inner, outer and self-joins.


3/48

May 3, 2012 3

Managing Expectations

Many sessions focus on latest and greatest features Sometimes, we miss out on the fundamentals

This talks subjects may seem old school But it applies to DBAs and developers (both client and web app)

Not everyone attending CodeCamp is advanced, oreven intermediate

Will see code, but purely SQL code As could be used in any app, and mostly any database

Will work mostly from slides, showing code andresults along with explanations Sure, I could have done all live demos and commentary

But this way you can download and review all the key points


4/48

May 3, 2012 4

About Your Speaker

CTO of Garrison Enterprises (Charlotte) 9 yrs Web App experience (23 yrs in Enterprise IT)

Frequent article/blog author, contributor to severalbooks, and 2 recent MSDN webcasts

Frequent speaker to user groups, conferencesworldwide


5/48

May 3, 2012 5

Slicing and Dicing Data in Many Ways

Theres more to database processing than simplyselecting columns for display. May want to massagethe data

There are many examples of more challenging SQL

problems, and lets consider several: Handling distinct column values

o Show each distinct lastname for employees

Manipulating data before or after selecting it

o Show the first 30 characters of a description column

o Find rows where the year in a date column is a particular year


6/48

May 3, 2012 6

Slicing and Dicing Data in Many Ways

As well as: Summarizing data

o Show how many employees we have

o Show how many employees make more than $40k

o Show how many employees have not been terminated

o Show the average, max, and min salary for all employees

o Show the total salary for all employees

o Show how many distinct salary levels there are

Sli i d Di i D i M W


7/48May 3, 2012 7

Slicing and Dicing Data in Many Ways(cont.)

As well as: Grouping Data

o Show those counts, averages, or totals by department

o Show those departments whose count/avg/total meets some criteria

Handling Nulls

o

Show employees who have not been terminated (TerminationDatecolumn is null)

o Count how many employees do not live in NYC

Cross-referencing tables

o Show each employee and their department

o Show all employees and their department, even if not assigned to one

o Show each employee and their manager

Ti 1 W ki ith D t i SQL V


8/48May 3, 2012 8

Tip 1: Working with Data in SQL VersusApplication Code

SQL provides the means to do each of those tasks And client code may have some means to do some of them

o Whether in Windows or web forms, C#, VB.NET, etc

Many developers create complicated code/scripts todo what SQL can enable with simpler constructs Same problems arise in other web app dev environments

Experienced developers/DBAs will admonish: Dont do things in code that you can better do in SQL

The challenge is deciding which to use

SQL 2005s support of CLR coding in SPs raises risk


9/48May 3, 2012 9

Tip 2: Client vs SQL Functions

Both SQL and client code offer functions for string, numeric,date, list and other manipulation Challenge is when to know which to use

Client applications (C#, VB.NET, etc.) offers hundreds offunctions These are used in a format such as Left(), DateFormat()

Used within scripts, these can even be used to build SQLo If used in building SQL, evaluated beforeSQL is passed to the DBMS

o If used in processing results, evaluated afterresults returned from the DBMS

SQL also offers several functions, as we will learn Also used in same format, such as Left()

Indeed, many share the same name!

Evaluated by DBMS whileprocessing the SQL to produce results Could indeed use both client code and SQL functions in a given

SQL statement Again, need to take care in deciding which to use

In this seminar, focus is on SQL functions


10/48May 3, 2012 10

Handling Distinct Column Values

Typical Problems: Show each distinct lastname for employees

Can try to do it manually, looping through all rowsand placing unique values in an array Tedious, Slow, Unnecessary!

SQL offers simple solution to produce list of uniquevalues

H dli Di ti t C l V l


11/48May 3, 2012 11

Problem: Show each distinct lastname for employees Solution: DISTINCTkeyword used before column

name

Example: (assuming we had a Lastname column)

Possible Query Result Set Values:Abbot

Brown

Coleman

Note: when used with multiple columns, DISTINCTmust be specified first. Applies to all columns Cant do SELECT Degree, DISTINCT Salary

Can do SELECT DISTINCT Salary, Degreeo Creates distinct instances of the combined values from each

Handling Distinct Column Values:DISTINCT Keyword

SELECT Distinct LastName

FROM EmployeesORDER BY Lastname


12/48May 3, 2012 12


Typical Problems: Show the first 30 characters of a description column

Find rows where the year in a date column is a particular year

Tempting to try with code functions in client app May be wasteful, or impossible

SQL functions may be more efficient, and could evenhave more features In any case, remember admonition:

o Dont do in client code that which you can do in SQL

Beware: while some SQL functions are shared by all DBMSs, eachsupports its own or variations

M i l ti D t ith SQL T t


13/48May 3, 2012 13

Manipulating Data with SQL: TextFunctions

Problem: Show the first 30 characters of adescription column Can certainly use most client languages Left() function to

substring the result passed back from SQL

o But this means sending all data from DB to the client (or ASP.NET),only to then be stripped down to 30 chars. Wasteful!

Solution: Use SQL Left() function

Example:

Note: There are many other similar text manipulation

functions, depending on DBMS Length(), Lower(), Upper(), Ltrim(), Soundex(), etc.

Investigate DBMS documentation to learn more

SELECT Left(Description,30) FROM Products

Manip lating Data ith SQL Date


14/48May 3, 2012 14

Manipulating Data with SQL: DateFunctions

Problem: Find rows where the year in a date columnis a particular year Assuming date column contains month, day, and year, how to just

search on year?

Could find records between 01/01/xx and 12/31/xx

Solution: Use SQL DatePart() function Example:

Note: each DBMS will have its own date handlingfunctions and function arguments This example is from SQL Server. Could also use Year(HireDate)

There are many other similar date manipulationfunctions, depending on DBMS Also will find numeric functions, system functions, and more

SELECT LastName FROM Employees

WHERE DatePart(yyyy,HireDate) = 2001


15/48May 3, 2012 15

Summarizing Data with SQL

Typical Problems: Show how many employees we have

Show how many employees make more than $40k

Count how many employees have not been terminated

Show the average, max, and min salary for all employees

Show the total salary for all employees Show how many distinct salary levels there are

Again, tempting to try with client code processing May be complicated, wasteful

SQL functions may be more efficient, more powerful

SQL functions for summarizing data are known as aggregatefunctions: Count, Min, Max, Avg, Sum

o Others include StdDev (standard deviation), Var (variance)

Summarizing Data with SQL: Count(*)


16/48

May 3, 2012 16

Summarizing Data with SQL: Count(*)Function

Problem: Show how many employees we have Newcomers may find all records and count on client/middle tier

o But if all we want is the count, this is wasteful!!!

Solution: Use SQL Count(*) function

Example:

Possible Query Result Set Values: 54

Notes: We use a column alias in order to refer to that count within code

Returns only a single-record resultseto In SQL Server, does clustered index scan

o Still, much faster than SELECT * and count in client application

o Should store the number in client app and reuse as long as possible

More on count(columnname) later

Other uses of count, to follow

SELECT Count(*) as RecCount

FROM Employees

Summarizing Data with SQL: Count(*)


17/48

May 3, 2012 17

Summarizing Data with SQL: Count(*)Function and Filter

Problem: Show how many employees make morethan $40k

Solution: Use SQL Count(*) function and a filter Simple matter of adding a WHERE clause to indicate the desired

criteria

Example: SELECT Count(*) as RecCountFROM Employees

WHERE Salary > 40000

Summarizing Data with SQL: Count(col)


18/48

May 3, 2012 18

Summarizing Data with SQL: Count(col)Function

Problem: Count how many employees have beenterminated

Solution: Use SQL Count(column) function Instead of counting all records, count all having a value for a given

column

Assume terminated employees have a value in theTerminationDate column

Example:

Note: doesnt count records having null column value Will discuss nulls later

In this case, the behavior is as expected. May not always be

SELECT Count(TerminationDate) as RecCount

FROM Employees

Summarizing Data with SQL:


19/48

May 3, 2012 19

Summarizing Data with SQL:AVG/MAX/MIN Functions

Problem: Show the average, max, and min salary forall employees

Solution: Use SQLAvg(), Min(), or Max() functions Besides just counting records having any value for a given column,

can also use these functions to summarize

Example:

Notes: Like Count(column) function, these functions ignores columns with

null valueso I.e., is average of records having a value for that column

Also, can add a filter in order to compute summaries for recordsmeeting some other criteria

SELECTAvg(Salary) as AvgSal, Min(Salary) as MinSal,

Max(Salary) as MaxSal

FROM Employees

Summarizing Data with SQL: SUM


20/48

May 3, 2012 20

Summarizing Data with SQL: SUMFunction

Problem: Show the total salary for all employees Solution: Use SQL Sum() function

Just as other functions compute Avg/Min/Max, can use Sumfunction to add up all values of column

Example:

Notes: Can also perform mathematical computation on the column and

sum that:SELECT SUM(Salary * 1.20)

Or perform computation between two or more columns and sum

that, as in:SELECT SUM(Salary*RaisePct)

SELECT Sum(Salary) as SumSal

FROM Employees

Summarizing Data with SQL: Using


21/48

May 3, 2012 21

Summarizing Data with SQL: UsingDISTINCT with Functions

Problem: Show how many distinct salary levels thereare

Solution: Use DISTINCT keyword with functions Rather than perform given function against all values of the given

column in all records, can performs it against only the unique

values that exist

Example:

Notes: Note that this will produce just one number: the number of distinct

salary values that existo To produce instead a count of employees at each salary level, need to

learn about SQL GROUP BYclause (coming next)

Can also use AVG (average of distinct values rather than of allvalues). MIN and MAX would return same result either way

SELECT Count(DISTINCT Salary) as NumDistinctSals

FROM Employees


22/48

May 3, 2012 22


Typical Problems: Show those counts, averages, or totals by department

Show which departments have count/avg/total meets some criteria

SQL provides a GROUP BY clause that can be used tocreate a list of unique values for a column Difference from DISTINCT is that it also rolls up the rows

o aggregates some computation over all the records having that uniquevalue


23/48

May 3, 2012 23


Assume the employees table has a Dept column Example:

Note: this simple example creates a result no

different than SELECT DISTINCT Dept You would nottypically use this statement, because youre also

asking the DB to roll up rows having the same value of Dept, butare aggregating nothing

Difference comes when combined with the previously presentedaggregate functions, which then aggregate the data BYthe unique

grouped column values

SELECT Dept FROM Employees

GROUP BY Dept

Grouping Data with SQL: Using GROUP BY


24/48

May 3, 2012 24

Grouping Data with SQL: Using GROUP BYwith Count Function

Problem: Show count of employees by department Solution: Use GROUP BY with COUNT(*) function

Example:

Possible QueryResult Set Values:

Notes: In example, first row in resultset represents records with

null value for Dept column

Order of rows is random. Could add ORDER BY Dept

o If present, must be specified AFTER the GROUP BY

SELECT Dept, Count(*) as CountEmp

FROM Employees

GROUP BY Dept

4

Sales 15

Engineering 33

Marketing 7



25/48

May 3, 2012 25

Grouping Data with SQL: Using GROUP BYwith Avg Function

Problem: Show average salary by department Solution: Use GROUP BY with Avg(column) function

Aggregate on a column other than that being grouped

Example:

Possible Query Result Set Values:

Notes: Could use Min/Max/Count(column) too

SELECT Dept, Avg(Salary) as AvgSalary

FROM Employees

GROUP BY Dept

45687

Sales 83276

Engineering 75500

Marketing 55000



26/48

May 3, 2012 26

Grouping Data with SQL: Using GROUP BYwith Functions

More notes: Columns to be SELECTed can only be aggregate functions and/or

column named in GROUP BY

o Could not use:

Since LastName isnt being GROUPed and isnt an aggregate function itself

Often a source of confusion, though it clearly wouldnt make sense to showLastName here

SELECT Lastname, Dept FROM Employees

GROUP BY Dept



27/48

May 3, 2012 27

Grouping Data with SQL: Using GROUP BYwith Filter

Problem: Show average salary by departments ofemployees whove completed grade 12

Solution: Use GROUP BY with filter WHERE clause limits which records are to be GROUPed

Example:

More notes: WHERE must occur after FROM, before GROUP

o Order of appearance:

FROM, WHERE, GROUP BY, ORDER BY

To select records whose aggregated values meet some criteria, useHAVING clause


FROM Employees

WHERE GradeCompleted >= 12

GROUP BY Dept



28/48

May 3, 2012 28

Grouping Data with SQL: Using GROUP BYwith HAVING

Problem: Show departments whose employees havean average salary greater than $40,000

Solution: Use GROUP BY with HAVING

Example:

Note: HAVING must occur after GROUP BY, before ORDER BY

Order of appearance:

o FROM, WHERE, GROUP BY, HAVING, ORDER BY

Expression in HAVING cant refer to alias from SELECT clauseo In example above, couldnt use HAVING AvgSalary > 40000


FROM Employees

GROUP BY DeptHAVING Avg(Salary) > 40000


29/48

May 3, 2012 29

Handling Nulls

About Nulls Columns that have no value are considered NULL

o Null is not the same as a space or 0 or empty string (). Its no valueat all

A column can be defined to not allow nulls

Can select which columns are or arent null with IS NULL or IS NOTNULL in WHERE clause

Typical Problems: Show employees who have not been terminated

Count how many employees do not live in NYC


30/48

May 3, 2012 30

Handling Nulls: Searching for Nulls

Problem: Show employees who have not beenterminated Assume TerminationDate is null if not yet terminated

Solution: Use IS NULL in WHERE clause

Example:SELECT LastName

FROM Employees

WHERE TerminationDate IS NULL

Handling Nulls: Negated Searching And


31/48

May 3, 2012 31

Handling Nulls: Negated Searching AndImpact of Nulls

Problem: Count how many employees do not live inNYC Be careful selecting records that dont have some given value

Tempting to use:Select count(*)

FROM Employees

WHERE City New York

Problem is it doesnt find records that dont have a value for cityo Consider 200 records: 10 in New York, 5 are null

o Is answer 185 or 190? Depends on if you think nulls count City New York ignores records with null values (null is neither equal to

nor not equal to new york

Solution: May want to add OR column IS NULL Example: SELECT Count(*)

FROM Employees

WHERE CITY New York

OR CITY IS NULL


32/48

May 3, 2012 32

Understanding Relational Database Design

Relational Databasesare comprised of several tables, eachstoring data about a particular aspect of the subject beingdescribed

Goals are: store only related data in a single table

dont repeat data (dont store it in more than one place)

ensure integrity of data cross-referenced between tables

Can be challenging to cross-reference that data

Personnel

Departments

Offices

Employees


33/48

May 3, 2012 33

Understanding Foreign Keys

Recall previous examples of GROUPing on Deptcolumn Assumed that Employees table had DEPT column holding string

values for department name

Problems with this include:o Were storing the same string multiple times on many records

o If a mistake is made entering a given value, that record will no longerbe found in searches on value (see EmpID 4)

Employees

EmpID Name HireDate Dept

1 Bob 06-04-98 Sales

2 Cindy 12-01-00 Engineering

3 John 01-01-01 Sales

4 Beth 05-30-99 Engineering


34/48

May 3, 2012 34

Understanding Foreign Keys

More appropriate solution: Have Department table with just a list of each valid Dept and a

unique DeptID (that tables primary key)

Then in Employees table, simply store that DeptID to indicate anemployees department

o This DeptID in the Employees table is called a Foreign Key Since it holds a value that comes from the primary key of another table

This is the fundamental aspect of a relational design

Employees

EmpID Name HireDate DeptID

1 Bob 06-04-98 1

2 Cindy 12-01-00 2

3 John 01-01-01 1

4 Beth 05-30-99 2

Departments

DeptID Dept

1 Sales

2 Engineering


35/48

May 3, 2012 35

Cross-Referencing Tables (Joins)

Typical Problems: Show each employee and their department

Show all employees and their department, even if not assigned toone

Show each employee and their manager

May be tempting for beginners to loop throughresultset of one query (departments) and search forrelated records (employees for each dept) Bad! Bad! Bad!

Correct solution is to instead JOIN the tables together

There are several kinds of joins, each serving different purposes


36/48

May 3, 2012 36

Understanding Joins

To retrieve data from multiple tables, simply list bothtables in FROM clause, such as:

Note that if columns of the same name existed in each table, wedneed to prefix the table name to the column

Only problem is that this selects all combinations ofthe values in the two columns In our example table, would create 8 rows in result

o 4 employees times 2 departments

Not really what we likely wanted

o Called a cartesian productor a cross join

Bob Sales

Cindy Sales

John Sales

Beth Sales

Bob Engineering

Cindy Engineering

John Engineering

Beth Engineering

SELECT Name, Dept

FROM Employees, Departments


37/48

May 3, 2012 37

Inner Joins

Problem: Show each employee and their department

Solution: Perform Inner Joinof the two tables indicate columns in each table that share common value. SQL

automatically matches themo Typically, where one tables foreign key maps to its corresponding

primary key in a related table

Example:

Correct Result:

Note: the datatype of the columns being joined mustmatch

SELECT Name, Dept

FROM Employees, Departments

WHERE Employees.DeptID = Departments.DeptID

Bob Sales

Cindy EngineeringJohn Sales

Beth Engineering


38/48

May 3, 2012 38

Join via WHERE vs JOIN clause

ANSI SQL standard (and most databases) supportsan alternative means of indicating joins Rather than indicate joined columns in WHERE clause

o Use them with JOIN keyword on FROM clause

Example:

Notes: If INNER keyword is not specified, INNER is assumed in SQL Server

Can join more than two tables with additional join clauses (of eitherformat)

o Any limit will be set by DBMS

o Practical limit is that performance suffers with too many joins in asingle SELECT

SELECT Name, Dept

FROM Employees INNER JOIN Departments

ON Employees.DeptID = Departments.DeptID


39/48

May 3, 2012 39

Outer Joins

With inner join, if value of join columns dont match,records will not be retrieved Unexpected problems can occur when foreign key is null

Assume we had at least one employee with nodepartment indicated (null value for DeptID)

With inner join, his record will not be displayed at all

o he has no DeptID to match on DeptIDs in Departments table Could be a real problem if expecting SELECT to show all

employees!

Employees

EmpID Name HireDate DeptID

5 Bill 11-22-00


40/48

May 3, 2012 40

Outer Joins

Problem: Show all employees and their department, even if not

assigned to one Solution: Perform Outer Joinof the two tables

Example:


Notes: This example indicated LEFT OUTER JOIN: there are 2 other types

o LEFT join means retrieve all rows from table on left of JOIN even if they donthave match for join column in right table

Creates null values in join columns that did not match

SELECT Name, Dept

FROM Employees LEFT OUTER JOIN Departments

ON Employees.DeptID = Departments.DeptID

Bob Sales

Cindy Engineering

John Sales

Beth Engineering

Bill


41/48

May 3, 2012 41

Outer Joins (cont.)

WHERE clause syntax for LEFT join:WHERE Employees.DeptID *= Departments.DeptID

Syntax no longer supported in SQL Server 2005o Must use sp_dbcmptlevel to set level to 80 or lower

Two other kinds of Outer joins: RIGHT OUTER JOIN retrieves all rows from table on right

o In current example, that would be useful if we had a row inDepartments not pointed to by an employee

A RIGHT join would then show a row in the resultset forAccounting (with name being null)

o Even though no employees had that DeptID

o WHERE clause syntax for RIGHT join (where supported):

WHERE Employees.DeptID =* Departments.DeptID

Departments

DeptID Dept

5 Accounting


42/48

May 3, 2012 42

Outer Joins (cont.)

Second kind of Outer join A FULL OUTER JOIN (or FULL JOIN) retrieves rows from both

tables even if join values dont match

o In current example, would show both:

a row for Bill with no department and

A row with no employee name for Accounting

No equivalent WHERE clause syntax at all

Bob Sales

Cindy Engineering

John Sales

Beth Engineering

Bill

Accounting


43/48

May 3, 2012 43

Self-Joins

Is possible to join a table to itself Assume Employees table has column for ManagerID,

to indicate each employees manager Values for that ManagerID column simply point to the EmpID for

their manager

How to show who works for who?

Employees

EmpID Name HireDate DeptID ManagerID

1 Bob 06-04-98 1 5

2 Cindy 12-01-00 2 4

3 John 01-01-01 1 1

4 Beth 05-30-99 2 5

5 Bill 10-10-97


44/48

May 3, 2012 44

Self-Joins

Problem: Show each employee and their manager

Solution: Use self-join (just join table to itself, using an alias) There is no SELF keyword

Example:


Pop Quiz/Final Exam: Why isnt Bill listed? We can see from others that hes the boss and is an employee

o He has null ManagerID

o To show him in table, what would we need ? An OUTER join

SELECT Employees.Name, Employees.Dept,Mgr.Name

as Manager

FROM Employees INNER JOIN Employees as Mgr

ON Employees.ManagerID =Mgr.EmpID

Name Dept Manager

Bob Sales Bill

Cindy Engineering Beth

John Sales Bob

Beth Engineering Bill


45/48

May 3, 2012 45

Summary

We learned how to slice and dice data in many ways Handling Distinct Column Values


Summarizing Data with SQL (Counts, Averages, etc.)


Handling Nulls We learned the value of other than inner joins

Use of outer joins, when join columns values may be null

Use of self-joins with hierarchical data


46/48

May 3, 2012 46

Some Other Tidbits for You to Investigate

TOP, TOP n PERCENT options on SELECT UNIONs

Nested Subqueries

EXISTS predicate


47/48

May 3, 2012 47

Where to Learn More

Popular SQL books available, including Teach Yourself SQL in 10 Minutes

o Excellent little guide to getting started

Practical SQL Handbook

SQL For Smarties (any Joe Celko book)

Learning SQL on SQL Server 2005 (OReilly)


48/48

Contact Information

With that, I want to thank you and hope you enjoyedthe talk

Contact for follow-up issues Email: [email protected]

Phone: (704) 650 5371

taking your sql beyond simple selects and (inner) joins

Documents