Sql Self Join

How to use Sql Self Join. A self join compares values within a single table.


Since we are using the same table more than once, we need to distinguish them by using different aliases. To qualify a column name, we use the alias name. See the following two different ways to write sql self join.

The Syntax

SELECT column_list
FROM   table_a a,
       table_a b
WHERE  a.id = b.id 

OR

SELECT column_list
FROM   table_a a
       JOIN table_a b
         ON a.id = b.id 

Example

Use PUBS datatabase. Sometimes a book has more than one authors. By using just TitleAuthors table, find a list of books that have more than one authors. For this sql self join exercise, just show TitleID and AuthorsID.

Sql Command

SELECT DISTINCT a.au_id,
                a.title_id
FROM   titleauthor a,
       titleauthor b
WHERE  a.title_id = b.title_id
       AND a.au_id != b.au_id
ORDER  BY title_id 

Results

au_idtitle_id
213-46-8915BU1032
409-56-7008BU1032
267-41-2394BU1111
724-80-9391BU1111
722-51-5454MC3021
899-46-2035MC3021
427-17-2319PC8888
846-92-7186PC8888
724-80-9391PS1372
756-30-7391PS1372
899-46-2035PS2091
998-72-3567PS2091
267-41-2394TC7777
472-27-2349TC7777
672-71-3249TC7777

Please note that the solution using sql self join is not the only way to answer that question. You can utilize Sql Count or Sql Subquery to solve this. Please try this yourself in your Sql Server Express environment.

You can find the solution in the Problems and Solutions page.

Quick link. From here, you can learn the following:

Data Creation

Sql Create Database
Use this to create a database.

Sql Create Table
This Sql Create Table will be used very often throughout the tutorial. It is used to create a table, in our case, mainly to create sample tables.

Sql Alter Table
Used less often, but it is very handy to know this command. With this you can basically edit a table structure.

Sql Drop Table
Used to delete a table, not just the content. This command is also used very often during the tutorial. So, you need to master this too.

Data Manipulation

Sql Insert
To add rows to a table, you use Sql Insert command. There are two methods, one is with the VALUES keyword and second is with a SELECT statement.

Sql Update
To change existing rows, you use Sql Update command. You can use it to change values in a single row, groups of rows or all rows. You can also update a table based on values in other tables.

Sql Delete
To remove rows, you use Sql Delete command. You can delete one rows, group of rows or all rows. You can also delete rows based on values in other tables.

Data Retrieval

Sql Select
The core of Sql, selecting data from table.

Sql Distinct
Eliminating duplicate values in the output.

Sql Where
Conditionally selecting rows from table.

Sql Like
Pattern searching for conditions in Where or Having clause

Sql Between
Added to conditions to show results between two values.

Sql In
It enables us to specify multiple values, either as a list or a subquery.

Sql And Or
Connecting two or more conditions in the Where or Having clause.

Sql Count
Counting number of rows.

Sql Sum
Getting the sum of a field

Sql Max
This function will return the highest value.

Sql Min
This function will return the lowest value.

Sql Avg
This function will return the average value.

Sql Order By
Sorting the output

Sql Group By
Grouping rows into sets.

Sql Having
To filter the result of aggregation

Advanced Sql Tutorial

Sql Inner Join
Use Sql Join to retrieve data from more than one table in just one Select statement.

Sql Outer Join
An Sql outer join shows the join, and all the rows that do not qualify.

Sql Subquery
A nested Sql Select inside the WHERE clause of another Sql Select statement.

Sql Union
Merging two or more queries into one result. Rows are appended from one queries to the other.

Perseverance: Cliffhanger
Buy at AllPosters.com
What the mind can conceive and believe, it can achieve.

Positive Resources

All of us have at least two or three things that excite us. We could talk about these subjects for hours. Good news!. Did you know that Site Built It! can turn this type of deep interest and enthusiasm into a revenue-generating business?

Review some of the Site Build It! case studies and you'll absolutely discover something that ignites more positive ideas into your mind.

SBI! Video Tour


Invest the most
important 30 minutes
you will spend online.

Click, sit back, and
understand why SBI!
makes you succeed.