Sql Inner Join

We'll discuss smart way to use sql inner join. And get the most out of rdbms by understanding this.


In a relational database that has been normalized, it's very rare you can get all information from just one table, you must put together data from several different tables. So using Sql join is inevitable. It lets you retrieve data from more than one table in a single Select statement.

The Sql Inner Join is best explained using pictures.

sql-join

Inner join, in set theory, is known as the intersection,i.e. elements which belong to both A and B.

You need to specify the connecting column or join column, and it has to be the same data type.

The syntax:

There are two ways to write Sql Inner Join

SELECT column_list
FROM   left_table_name a,
       right_table_name b
WHERE  a.id = b.id 

OR (check if your system permits the following syntax)

SELECT column_list
FROM   left_table_name AS a
       INNER JOIN right_table_name AS b
         ON a.id = b.id 

Where Arguments

column_list
List of columns to select from either of the tables. All column references must be unambiguous. You can also use an expression.

left_table_name / right_table_name
The two tables to be joined together

as A / as B
Aliases. It is useful to use alias, mainly to shorten the table reference. It makes the Sql more readable.

INNER JOIN
An explicit method of inner join. Join column between Tbl_L and Tbl_R.

ON A.ID=B.ID
Join column between Tbl_L and Tbl_R.

WHERE A.ID=B.ID
An implicit method of inner join. ID is the join column between Tbl_L and Tbl_R. Noticed how much simpler this comparison is because of using alias.

Example

To illustrate let's use PUBS database. This is some entries of Titles table:

Titles

title_idtitletypepub_id
BU1032The Busy Executive's Database Guidebusiness 1389
BU1111Cooking with Computers: Surreptitious Balance Sheetsbusiness 1389
BU2075You Can Combat Computer Stress!business 736
BU7832Straight Talk About Computersbusiness 1389
MC2222Silicon Valley Gastronomic Treatsmod_cook 877

In the Titles table, the publisher's information is just a number. The number is the ID of the publishers which is stored in Publishers table. The task is simply to show the publisher's name for each book's title.

This is some entries from Publishers table.

Publishers

pub_idpub_namecitystatecountry
736New Moon BooksBostonMAUSA
877Binnet & HardleyWashingtonDCUSA
1389Algodata InfosystemsBerkeleyCAUSA
1622Five Lakes PublishingChicagoILUSA
1756Ramona PublishersDallasTXUSA

SQL statement:

SELECT title_id,
       title,
       TYPE,
       pub_name
FROM   titles AS tl
       INNER JOIN publishers AS pb
         ON tl.pub_id = pb.pub_id 

Some of rows from the result:

title_idtitletypepub_name
BU1032The Busy Executive's Database Guidebusiness Algodata Infosystems
BU1111Cooking with Computers: Surreptitious Balance Sheetsbusiness Algodata Infosystems
BU2075You Can Combat Computer Stress!business New Moon Books
BU7832Straight Talk About Computersbusiness Algodata Infosystems
MC2222Silicon Valley Gastronomic Treatsmod_cook Binnet & Hardley

Please notice the following:

  • SELECT clause: you can omit the alias in the columns selection only if you are sure that the column name exists only in one of the table. If a field exists in both tables, use the table alias.
  • The above sql inner join is an explicit join. You can also achieve the same result using implicit sql join
  • SELECT title_id,
           title,
           TYPE,
           pub_name
    FROM   titles tl,
           publishers pb
    WHERE  tl.pub_id = pb.pub_id 

That's the simplest use of the Sql inner join. To see more examples of Sql inner join, please visit Problems and Solutions page.

page border

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 Outer Join
An Sql outer join shows the join, and all the rows that do not qualify.

Sql Self Join
A join that compares values within a single table.

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.

Journey
Buy at AllPosters.com
A bend in the road is not the end of the road... unless you fail to make the turn

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.