SQL

####1###nth highest #####3rd
WITH result AS
(
SELECT
salary
, dense_rank over (ORDER BY salary DESC) AS denserank
)

SELECT *
FROM   result
WHERE  result.denserank = 3

####2###nth highest #####
SELECT TOP 1 salary
FROM   (SELECT DISTINCT TOP 2 salary
        ORDER  BY salary DESC)
ORDER  BY salary

###3###ASC & DESC #####
SELECT *
FROM   customers
ORDER  BY nameid ASC,country DESC

###4###get organization hierarchy #####
DECLARE @ID INT;
SET @ID =7;

WITH employeecte
     AS (SELECT empid,
                empname,
                magid
         FROM   employees
         WHERE  empid = @ID
         UNION ALL
         SELECT employees.empid,
                employees.empname,
                employees.magid
         FROM   employees
                JOIN employeecte
                  ON employee.empid = employeecte.magid)
SELECT E1.emplyee,
       Isnull(E2.empname, 'NoBoss') AS ManagerName
FROM   employeecte E1
       LEFT JOIN employeecte E2
              ON E1.managerid = E2.employid

###5###rows cantain only numerical data #####
SELECT value
FROM   testtable
WHERE  Isnumeric (value) = 1

###6###Employees hired in last months #####
SELECT   *
FROM     emploees
WHERE    Datadiff(month,hiredate,Getdate()) berween 1
AND      3
ORDER BY hiredata DESC

###7###employee hired in first half of the month####
SELECT *
FROM   employee e
WHERE  To_number(To_char(e.datehired, 'DD')) <= 15

###8###department wtih highest number of employee####
SELECT TOP 1
         departmentname
FROM     employee
JOIN     department
ON       employees.departmentid = depaermtnets departmentid
GROUP BY departmentname
ORDER BY count (*) DESC

###9.1###calculate percentage####
SELECT grade,
       Count(*) * 100 / Sum(Count(*))
                          OVER ()
FROM   mytable
GROUP  BY grade

###9.2###calculate percentage####
SELECT grade,
       Count(*) * 100 / (SELECT Count(*)
                         FROM   mytable)
FROM   mytable
GROUP  BY grade

###10###delete duplicate rows####
WITH employeecte
     AS (SELECT *,
                Row_number()
                  OVER (
                    partition BY id
                    ORDER BY id) AS RowNumber
         FROM   employees)
DELETE FROM employeescte
WHERE  rownumber > 1

###11###datediff example####

SELECT Start_Date, End_Date
FROM 
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
    (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b 
WHERE Start_Date < End_Date
GROUP BY Start_Date 
ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date

Introduction

This is just a simple article visually explaining SQL JOINs.

Background

I'm a pretty visual person. Things seem to make more sense as a picture. I looked all over the Internet for a good graphical representation of SQL JOINs, but I couldn't find any to my liking. Some had good diagrams but lacked completeness (they didn't have all the possible JOINs), and some were just plain terrible. So, I decided to create my own and write an article about it.

Using the code

I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins. The seven Joins I will discuss are shown below:

INNER JOIN
LEFT JOIN
RIGHT JOIN
OUTER JOIN
LEFT JOIN EXCLUDING INNER JOIN
RIGHT JOIN EXCLUDING INNER JOIN
OUTER JOIN EXCLUDING INNER JOIN

For the sake of this article, I'll refer to 5, 6, and 7 as LEFT EXCLUDING JOIN, RIGHT EXCLUDING JOIN, andOUTER EXCLUDING JOIN, respectively. Some may argue that 5, 6, and 7 are not really joining the two tables, but for simplicity, I will still refer to these as Joins because you use a SQL Join in each of these queries (but exclude some records with a WHERE clause).

Inner JOIN

This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

Hide Copy Code

SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

Left JOIN

This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:

Hide Copy Code

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

Right JOIN

This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:

Hide Copy Code

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

Outer JOIN

This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

Hide Copy Code

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

Left Excluding JOIN

This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

Hide Copy Code

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Right Excluding JOIN

This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

Hide Copy Code

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

Outer Excluding JOIN

This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. I have yet to have a need for using this type of Join, but all of the others, I use quite frequently. This Join is written as follows:

Hide Copy Code

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

Examples

Suppose we have two tables, Table_A and Table_B. The data in these tables are shown below:

Hide Copy Code

TABLE_A
  PK Value
---- ----------
   1 FOX
   2 COP
   3 TAXI
   6 WASHINGTON
   7 DELL
   5 ARIZONA
   4 LINCOLN
  10 LUCENT

TABLE_B
  PK Value
---- ----------
   1 TROT
   2 CAR
   3 CAB
   6 MONUMENT
   7 PC
   8 MICROSOFT
   9 APPLE
  11 SCOTCH

The results of the seven Joins are shown below:

Hide Copy Code

-- INNER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7

(5 row(s) affected)

Hide Copy Code

-- LEFT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
  10 LUCENT     NULL       NULL

(8 row(s) affected)

Hide Copy Code

-- RIGHT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(8 row(s) affected)

Hide Copy Code

-- OUTER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(11 row(s) affected)

Hide Copy Code

-- LEFT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
  10 LUCENT     NULL       NULL
(3 row(s) affected)

Hide Copy Code

-- RIGHT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(3 row(s) affected)

Hide Copy Code

-- OUTER EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(6 row(s) affected)

Note on the OUTER JOIN that the inner joined records are returned first, followed by the right joined records, and then finally the left joined records (at least, that's how my Microsoft SQL Server did it; this, of course, is without using any ORDER BY statement).

You can visit the Wikipedia article for more info here (however, the entry is not graphical).

I've also created a cheat sheet that you can print out if needed. If you right click on the image below and select "Save Target As...", you will download the full size image.

SQL

Monday, December 13, 2021

关于认真生活

Saturday, October 15, 2016

SQL Stuff

Wednesday, June 15, 2016

SQL course

Monday, June 6, 2016

Top 80 + SQL Query Interview Questions and Answers with Examples

Saturday, November 14, 2015

SQL interview quesstion

Tuesday, March 17, 2015

Visual Representation of SQL Joins

Introduction

Background

Using the code

Inner JOIN

Left JOIN

Right JOIN

Outer JOIN

Left Excluding JOIN

Right Excluding JOIN

Outer Excluding JOIN

Examples