• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Computer Notes

Library
    • Computer Fundamental
    • Computer Memory
    • DBMS Tutorial
    • Operating System
    • Computer Networking
    • C Programming
    • C++ Programming
    • Java Programming
    • C# Programming
    • SQL Tutorial
    • Management Tutorial
    • Computer Graphics
    • Compiler Design
    • Style Sheet
    • JavaScript Tutorial
    • Html Tutorial
    • Wordpress Tutorial
    • Python Tutorial
    • PHP Tutorial
    • JSP Tutorial
    • AngularJS Tutorial
    • Data Structures
    • E Commerce Tutorial
    • Visual Basic
    • Structs2 Tutorial
    • Digital Electronics
    • Internet Terms
    • Servlet Tutorial
    • Software Engineering
    • Interviews Questions
    • Basic Terms
    • Troubleshooting
Menu

Header Right

Home » Sql » Commands » SQL INNER JOIN
Next →
← Prev

SQL INNER JOIN

By Dinesh Thakur

In SQL inner joins are also called simple joins or equijoin. We are now ready to present a SELECT statement with what is called an inner join:

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

Let’s examine this statement line by line. The SELECT keyword on the first line merely states that we want all (*) columns from both tables. The second line, with the FROM clause, indicates that the first table we want to specify is the emp table.

The third line introduces a new INNER JOIN keyword. This keyword is used to specify an additional table that we want to join to. In this case, we want to add in the dept table.

Finally, the fourth line introduces the ON keyword. The ON keyword works in conjunction with the INNER JOIN. The ON specifies exactly how the two tables are to be joined. In this case, we are connecting the deptno column of the emp table (emp.deptno) to the deptno column of the dept table (dept.deptno). Since the deptno column has the same name in both the emp and dept table, we need to specify the table name as a prefix to the deptno column name in the ON clause. The prefix allows us to distinguish between these columns in two separate tables.

The above SELECT produces this data:

Let’s analyze the results.

•   The SELECT * clause specifies the column names to retrieve:

•   The FROM clause specifies the two tables that the database must access:

       • EMP table

       • DEPT table

•   The WHERE clause specifies how the tables are to be joined:

     EMP.DEPTNO = DEPT.DEPTNO

Because the DEPTNO column is common to both tables, it must be prefixed by the table name to avoid ambiguity.

This brings us to this important observation: An inner join only brings back data for which there is a match between both tables being joined.

A nonequijoin is a join condition containing something other than an equality operator. The relationship between the EMP table and the SALGRADES table has an example of a nonequijoin. A relationship between the two tables is that the SAL column in the EMP table must be between the values in the LOSAL and HISAL columns of the SALGRADES table. The relationship is obtained using an operator other than equals (=).

SELECT e.ename, e.sal, s.grade FROM emp e, salgrades s WHERE e.sal BETWEEN s.losal AND s.hisal;

The example of nonequijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges.

It is important to note that all employees appear exactly once when this query is executed. No employee is repeated in the list. There are two reasons for this:

• None of the rows in the salgrade table contain grades that overlap. That is, the salary value for an employee can lie only between the low salary and high salary values of one of the rows in the salgrade table.

• All of the employees’ salaries lie within the limits provided by the job grade table. That is, no employee earns less than the lowest value contained in the LOSAL column or more than the highest value contained in the HISAL column.

Note: Other conditions, such as <= and >= can be used, but BETWEEN is the simplest. Remember to specify the low value first and the high value last when using BETWEEN.

You’ll also like:

  1. SQL Self JOIN
  2. SQL CROSS JOIN
  3. SQL RIGHT JOIN Keyword
  4. SQL LEFT OUTER JOIN
  5. SQL FULL OUTER JOIN Keyword
Next →
← Prev
Like/Subscribe us for latest updates     

About Dinesh Thakur
Dinesh ThakurDinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps.

Dinesh Thakur is a Freelance Writer who helps different clients from all over the globe. Dinesh has written over 500+ blogs, 30+ eBooks, and 10000+ Posts for all types of clients.


For any type of query or something that you think is missing, please feel free to Contact us.


Primary Sidebar

SQL Tutorials

SQL Tutorials

  • SQL - Home
  • SQL - Select
  • SQL - Create
  • SQL - View
  • SQL - Sub Queries
  • SQL - Update
  • SQL - Delete
  • SQL - Order By
  • SQL - Select Distinct
  • SQL - Group By
  • SQL - Where Clause
  • SQL - Select Into
  • SQL - Insert Into
  • SQL - Sequence
  • SQL - Constraints
  • SQL - Alter
  • SQL - Date
  • SQL - Foreign Key
  • SQL - Like Operator
  • SQL - CHECK Constraint
  • SQL - Exists Operator
  • SQL - Drop Table
  • SQL - Alias Syntax
  • SQL - Primary Key
  • SQL - Not Null
  • SQL - Union Operator
  • SQL - Unique Constraint
  • SQL - Between Operator
  • SQL - Having Clause
  • SQL - Isnull() Function
  • SQL - IN Operator
  • SQL - Default Constraint
  • SQL - Minus Operator
  • SQL - Intersect Operator
  • SQL - Triggers
  • SQL - Cursors

Advanced SQL

  • SQL - Joins
  • SQL - Index
  • SQL - Self Join
  • SQL - Outer Join
  • SQL - Join Types
  • SQL - Cross Join
  • SQL - Left Outer Join
  • SQL - Right Join
  • SQL - Drop Index
  • SQL - Inner Join
  • SQL - Datediff() Function
  • SQL - NVL Function
  • SQL - Decode Function
  • SQL - Datepart() Function
  • SQL - Count Function
  • SQL - Getdate() Function
  • SQL - Cast() Function
  • SQL - Round() Function

Other Links

  • SQL - PDF Version

Footer

Basic Course

  • Computer Fundamental
  • Computer Networking
  • Operating System
  • Database System
  • Computer Graphics
  • Management System
  • Software Engineering
  • Digital Electronics
  • Electronic Commerce
  • Compiler Design
  • Troubleshooting

Programming

  • Java Programming
  • Structured Query (SQL)
  • C Programming
  • C++ Programming
  • Visual Basic
  • Data Structures
  • Struts 2
  • Java Servlet
  • C# Programming
  • Basic Terms
  • Interviews

World Wide Web

  • Internet
  • Java Script
  • HTML Language
  • Cascading Style Sheet
  • Java Server Pages
  • Wordpress
  • PHP
  • Python Tutorial
  • AngularJS
  • Troubleshooting

 About Us |  Contact Us |  FAQ

Dinesh Thakur is a Technology Columinist and founder of Computer Notes.

Copyright © 2023. All Rights Reserved.