SQL INNER JOIN - Join Multiple Tables, Inner Join on Same Table, Inner Join vs Left Join vs Outer Join

The most frequently used and important of the joins is the SQL INNER JOIN.

The SQL INNER JOIN clause selects all records or rows from both tables (tableA and tableB) and returns a result table by combining field or column values of two tables that is limited to the records as long as where there is a match keys in both tables.

The INNER JOIN query retrieves matched records from two or more linked tables based on the condition supplied. The Inner Join keyword must be inserted after the FROM clause in the SELECT statement. An EQUIJOIN is another name for it.

Because MySQL treats it as a default Join, using the Inner Join keyword with the query is optional.

To identify all pairs of rows that satisfy the join-predicate, the query compares each row of table1 with each row of table2. Column values for each matched pair of rows of A and B are combined into a result row when the join-predicate is met.

Syntax:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

In this syntax, we must first choose the column list, then specify the table name that will be joined to the main table (table1, table2), and then give the condition after the ON keyword. The Join condition returns the rows that fulfill the Inner clause's specified tables.

Example 1: The following statement uses two INNER JOIN clauses to query data from the three tables:

SELECT product_name,
    category_name,
    brand_name,
    list_price
FROM
    production.products p
INNER JOIN production.categories c ON c.category_id = p.category_id
INNER JOIN production.brands b ON b.brand_id = p.brand_id
ORDER BY
    product_name DESC;

Example 2: Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+
| OID | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now, let us join these two tables using the INNER JOIN as follows :

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output:

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|  3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|  2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|  4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+

Example 3: To select records from both tables, execute the following query:

SELECT students.stud_fname, students.stud_lname, students.city, technologies.technology    
FROM students   
INNER JOIN technologies    
ON students.student_id = technologies.tech_id;

Example 4: For the demo purpose, we will use the following Employee and Department tables in all examples.

Employee Table

EmpId	FirstName	LastName	Email			Salary	DeptId
1	'John'		'King'		'john.king@abc.com'	33000	1
2	'James'		'Bond'			
3	'Neena'		'Kochhar'	'neena@test.com'	17000	2
4	'Lex'		'De Haan'	'lex@test.com'		15000	1
5	'Amit'		'Patel'					18000	3
6	'Abdul'		'Kalam'		'abdul@test.com'	25000	2

Department Table

DeptId	Name
1	'Finance'
2	'HR'

Consider the following inner join query.

SELECT Employee.EmpId, Employee.FirstName, Employee.LastName, Department.Name 
FROM Employee 
INNER JOIN Department
ON Employee.DeptId = Department.DeptId;

The following inner join query joins the Employee and Department databases, retrieving records from both tables where Employee is present. Employee.DeptId = Department. DeptId. Only records from both tables where DeptId in the Employee table matches DeptId in the Department table are retrieved. It will not fetch those records if the DeptId is NULL or does not match.

Output:

EmpId	FirstName	LastName	Name
1	'John'		'King'		'Finance'
3	'Neena'		'Kochhar'	'HR'
4	'Lex'		'De Haan'	'Finance'
6	'Abdul'		'Kalam'		'HR'

Notice that it only displayed the records whose DeptId matches, not whose DeptId is null or not matching.

When you use the INNER JOIN keyword with the select statement, it creates a new result table by joining the three tables above and returning the matching records from each table.

Syntax:

You can join 3 tables using the following INNER JOIN syntax:

SELECT table1.column1_name, table1.column2_name,..., 
       table2.column1_name, table2.column2_name,..., 
       table3.column1_name, table3.column2_name,..., 
FROM table1
INNER JOIN table2 ON table1.table1_id = table2.table1_id 
INNER JOIN table3 ON table2.table2_id  = table3.table2_id;

Example 1: Consider the three tables below:

fist table "videogame"

id	title			reg_date	Content
1	BioShock Infinite	2016-08-08	....

"tags" table

id	name
1	yennefer
2	elizabeth

"tags_meta" table

post_id	tag_id
1	2

SELECT videogame.id,
    videogame.title,
    videogame.reg_date,
    tags.name,
    tags_meta.post_id
FROM tags_meta
INNER JOIN videogame ON videogame.id = tags_meta.post_id
INNER JOIN tags ON tags.id = tags_meta.tag_id
WHERE tags.name = "elizabeth"
ORDER BY videogame.reg_date

Example 2:

SELECT students.full_name, students.gender, contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
INNER JOIN students ON universities.university_id = students.university_id
INNER JOIN contacts ON students.student_id = contacts.student_id;

Example 3: We can use the same techniques for joining three tables. The following query selects productID, productName, categoryName and supplier from the products, categories and suppliers tables:

SELECT productID,
    productName,
    categoryName,
    companyName AS supplier
FROM
    products
INNER JOIN
    categories ON categories.categoryID = products.categoryID
INNER JOIN
    suppliers ON suppliers.supplierID = products.supplierID

Example 4: Inner Join on three tables student, course, and lecturer:

SELECT *
FROM student  
INNER JOIN  
course  
ON  
student.course_id = course.course_id
INNER JOIN  
lecturer  
ON  
student.lecturer_id = lecturer.lecturer_id;

If you use INNER Link to join four tables, the result will be a new table with only matching values from all four tables.

Syntax :

SELECT table1.column1_name, table1.column2_name,..., 
       table2.column1_name, table2.column2_name,..., 
       table3.column1_name, table3.column2_name,..., 
       table4.column1_name, table4.column2_name,..., 
FROM table1
INNER JOIN table2 ON table1.table1_id = table2.table1_id 
INNER JOIN table3 ON table2.table2_id  = table3.table2_id;
INNER JOIN table4 ON table3.table3_id = table4.table3_id;

Query :

SELECT courses.course_name, 
subjects.subject_name,
chapters.chapter_name, 
subchapters.subchapter_name 
FROM courses
INNER JOIN subjects ON courses.course_id = subjects.course_id
INNER JOIN chapters ON subjects.subject_id = chapters.subject_id
INNER JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id;

When you need all records from the "left" table, irrespective of whether they have pair in the "right" table, use INNER JOIN, and when you need all records from the "right" table, use LEFT JOIN.

INNER JOIN should be used only when it is not possible to utilise another JOIN, and replicating INNER JOIN with other JOINS will reduce performance. Any OUTER JOIN that can be converted to an INNER JOIN should be done as soon as possible.

Example 1: Run following two script and observe the resultset. Resultset will be identical.

USE AdventureWorks
GO

Example of INNER JOIN

SELECT p.ProductID, piy.ProductID
FROM Production.Product p
INNER JOIN Production.ProductInventory piy ON piy.ProductID = p.ProductID
GO

Example of LEFT JOIN simulating INNER JOIN

SELECT p.ProductID, piy.ProductID
FROM Production.Product p
LEFT JOIN Production.ProductInventory piy ON 1 = 1
WHERE piy.ProductID = p.ProductID
GO

After seeing identical results, the first thing that came to mind was what was going on behind the scenes. When reviewing at the query's actual execution plan, it's evident that LEFT JOIN is used. The SQL Server Query Optimizer changes it to an INNER JOIN because it determines that an OUTER LEFT JOIN is unnecessary and that an INNER JOIN will provide improved results.

Example 2:

SELECT questions.TEXT
     , assignments_questions.question_id
     , assignments_solutions.submitted_solution
  FROM questions
 INNER JOIN assignments_questions ON questions.question_id = assignments_questions.question_id
  LEFT JOIN assignments_solutions ON questions.question_id = assignments_solutions.question_id
 WHERE assignment_id = 208

Example 1: SQL INNER JOIN with CASE statement:

SELECT employee_id as EmployeeID, 
    first_name as FirstName,  
    last_name as LastName, 
    hire_date as HireDate, 
    t2.job_title as JobTitle, 
CASE 
    WHEN hire_date <= '31-DEC-03' THEN 'Old Employee' 
    ELSE 'New Employee' 
END AS EmployeeStatus 
FROM hr.employees t1 
INNER JOIN hr.jobs t2 ON t1.job_id = t2.job_id;

Example 2: It seems like you are trying to create Where-clauses in the case, but you should instead compare the result of the case against Call_Type_ID(or any other field you want) as in the example i wrote below Hope it helps:

Also sometimes i use brackets over my casing to make it easier to see where they start and stop.

INNER JOIN datamartend.dbo.Call_Type_Dim ON 
(CASE 
 WHEN  CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned  IS NULL
     AND AnsTime > 0
     AND CallState IS NULL
     THEN 10
WHEN CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned  IS NULL
     AND AnsTime > 0  
     AND CallState = 1
     THEN 11
WHEN 
     CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned = 1
     AND AnsTime IS NULL
     AND CallState IS NULL
     THEN 12
ELSE 1
END) = Call_Type_ID  -- Insert something here to join on.

COUNT can also be used in conjunction with INNER JOIN statements. COUNT can be used with any combination of a query that operates on a single table or several tables using JOINS because it pertains to the number of rows.

Example 1: To count records in more than one table and how to use case for printing message.

select COUNT( * ) as 'Num of Role', Types = (    
    case when UserRole.Id = 1 then 'Created By Admin'    
    when UserRole.Id = 8 then 'Created By Creator'    
    end) from Model inner join dbo.[User] on Model.UserId = [User].Id    
inner join UserRole on dbo.[User].UserRoleId = dbo.[UserRole].Id Group By UserRole.Id 

Example 2: SQLite COUNT(*) with INNER JOIN clause example:

You can include the album's name column in the above query's output to make it more useful. You achieve this by adding INNER JOIN and ORDER BY clauses to the query, as seen below:

SELECT
	tracks.albumid, 
	title, 
	COUNT(*)
FROM
	tracks
INNER JOIN albums ON
	albums.albumid = tracks.albumid
GROUP BY
	tracks.albumid
HAVING
	COUNT(*) > 25
ORDER BY
	COUNT(*) DESC;

Output:

AlbumId  Title           COUNT(*)
-------  --------------  --------
141      Greatest Hits   57
23       Minha Historia  34
73       Unplugged       30
229      Lost, Season 3  26

Example 3: Example: Join both product_details and category_details table and find count by category_name from the product_details table.

SELECT category_name, COUNT(category_name) from product_details pd INNER 
JOIN category_details cd ON cd.category_id = pd.category_id
GROUP BY category_name;

Output:

category_name	COUNT(category_name)
FMCG		    7
FURNITURE	    1
APPLIANCES	    1
ELECTRONICS	    2

Example 4:

• Table A has a bunch of listings with Unique ID Numbers.
• Table B has information about who voted on table A.

So there can be 4 entries in Table B, and if they all have the "id" of a corresponding id from Table A.. That means the listing in Table A has 4 votes to it.

TABLE A ( listings )

id      title         description     params      visible
A1     "Title"    "Description"       "Params"    1
A2     "Title2"   "Description"       "Params"    1
A3     "Title3"   "Description"      "Params"     1

TABLE B ( votes )

uniqID     id        name      	 browser     	  date
1          "A1"     "Matt"      "Mozilla"        12/12/12
2          "A1"    "JohN"      " IE"             10/12/08
3          "A2"    "Greg"       "Safari"         10/10/10

This shows that A1 listing has two votes.

I wrote a few queries that sort of worked but not totally. I want to get back the id , title, description, params, visible, count( from table b ) per listing

SELECT p.id, p.worktitle, p.enddate, p.views, b.allBids
FROM postings p
INNER JOIN (
      SELECT pid, COUNT(*) AS allBids
      FROM bids
      GROUP BY pid
      )b ON b.pid = p.id
WHERE p.createdby='4'
ORDER BY p.enddate ASC
LIMIT 0,30

The INNER JOIN clause is used to query data from various tables, while the GROUP BY clause is used to group rows into a collection of summary rows.

Example 1: COUNT the INNER JOIN and GROUP BY wrap that query into another query:

SELECT COUNT(*) FROM (
    SELECT COUNT(table1.act) AS actCount FROM table1
    INNER JOIN table2 ON table1.act = table2.act
    GROUP BY table1.act
) t

Example 2: The following statement joins the tracks table with the albums table to get the album’s titles and uses the GROUP BY clause with the COUNT function to get the number of tracks per album.

SELECT tracks.albumid,
	title,
	COUNT(trackid)
FROM
	tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
	tracks.albumid;

Example 3: The following statement uses the COUNT(*) function to find the number of products in each product category:

SELECT category_name,
    COUNT(*) product_count
FROM
    production.products p
    INNER JOIN production.categories c 
    ON c.category_id = p.category_id
GROUP BY 
    category_name
ORDER BY
    product_count DESC;

Output:

category_name        product_count
-------------------- -------------
Cruisers Bicycles    78
Mountain Bikes       60
Road Bikes           60
Children Bicycles    59
Comfort Bicycles     30
Electric Bikes       24
Cyclocross Bicycles  10

The COUNT() method is applied to each group in this example after the GROUP BY clause separated the products into groups using category names.

Example 4:

SELECT songs.name,
 average_rating,
 total_ratings,
 total_bookmarks
FROM
 songs
 INNER JOIN
  (
   SELECT
   song_id,
   AVG(rating) AS average_rating,
   COUNT(*) AS total_ratings
FROM
   ratings
   GROUP
   song_id
  ) AS ratings_summary
   ON ratings_summary.song_id = songs.song_id
   LEFT JOIN
  (
   SELECT song_id,
   COUNT(*) AS total_bookmarks
FROM
   bookmarks
   GROUP BY song_id
  ) AS bookmarks_summary
   ON bookmarks_summary.song_id = songs.id
ORDER BY
    total_ratings DESC;

The question above is valid, but it takes 5 seconds to complete. It takes one-tenth of a second if I remove ORDER BY. It takes one-tenth of a second to eliminate one of the sub-queries (derived tables).

If you're simply connecting two tables, using COUNT with GROUP BY is acceptable, but when you add a second one-to-many table to the mix, it throws all the aggregate functions out of whack because the extra rows multiply against each other.

A nonrecursive CTE is one that does not include any references to itself. Nonrecursive CTEs are easier to understand than recursive CTEs, which is why I'm beginning here.

Example 1: SQL CTE with INNER JOIN

WITH salesrep AS (
    SELECT 
        employeeNumber,
        CONCAT(firstName, ' ', lastName) AS salesrepName
    FROM
        employees
    WHERE
        jobTitle = 'Sales Rep'
),
customer_salesrep AS (
    SELECT 
        customerName, salesrepName
    FROM
        customers
            INNER JOIN
        salesrep ON employeeNumber = salesrepEmployeeNumber
)

SELECT 
    *
FROM
    customer_salesrep
ORDER BY customerName;

Example 2: In the following example, I create a CTE named cteTotalSales:

WITH 
  cteTotalSales (SalesPersonID, NetSales)
  AS
  (
    SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
    FROM Sales.SalesOrderHeader 
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
  )
SELECT 
  sp.FirstName + ' ' + sp.LastName AS FullName,
  sp.City + ', ' + StateProvinceName AS Location,
  ts.NetSales
FROM Sales.vSalesPerson AS sp
  INNER JOIN cteTotalSales AS ts
    ON sp.BusinessEntityID = ts.SalesPersonID
ORDER BY ts.NetSales DESC

I offer two column names, SalesPersonID and NetSales, which are surrounded in parenthesis and separated by a comma after I specify the CTE name. That implies the CTE query must give two columns as a result set.

Example 3: INNER JOIN combines the CTE and introduces recursion, in which the intern refers to itself. The INNER JOIN obtains data by partitioning the "MyDepartment" table into two pieces using OrgTree CTE, then joining the two tables and creating the CTE for us to query :

WITH OrgTree(DepartmentName,ParentID,ReportsTo)AS
(
   SELECT T1.DepartmentName,T2.DepartmentID,T2.DepartmentName 
   FROM MyDepartment T1
   INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID 
   WHERE T1.DepartmentID=6
   UNION ALL
   SELECT OT.ReportsTo,T2.DepartmentID,T2.DepartmentName
   FROM OrgTree OT
   INNER JOIN MyDepartment T1 ON OT.ParentID=T1.DepartmentID
   INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID
)

SELECT * FROM OrgTree;

Example 4: Joining a CTE with a table example

In the following example, we will use the rental and staff tables:

The following statement illustrates how to join a CTE with a table:

WITH cte_rental AS (
    SELECT staff_id,
        COUNT(rental_id) rental_count
    FROM   rental
    GROUP  BY staff_id
)

SELECT s.staff_id,
    first_name,
    last_name,
    rental_count
FROM staff s
    INNER JOIN cte_rental USING (staff_id); 

In this example:

• First, the CTE returns a result set that includes staff id and the number of rentals.
• Then, join the staff table with the CTE using the staff_id column.

You can also delete rows from one table and the corresponding rows from another table using the INNER JOIN clause in the DELETE command in MySQL.

Syntax:

SQL Syntax for delete JOIN

DELETE [target table]  
FROM    [table1]  
        INNER JOIN [table2]  
ON [table1.[joining column] = [table2].[joining column]  
WHERE   [condition]

Example 1: SQL Query to Illustrate DELETE INNER JOIN

Delete with inner join is used to delete all the records from the first table and all the matching records from the second table. In order to understand the concept better, we will take the help of two tables, Employees (this contains personal details of all the employees) and departments (it contains details like department id, name, and its hod).

The data in the department’s table look something like this:

departmentid	departmentname		head
4001		Sales & Marketing	Lina Krishna
4002		Products		David Jackson
4003		Human Resources		David Mayers

The data in the employee’s table is as follows:

employeeid	lastname	firstname	departmentid	address		city
10028		Becker		Todd		4001		27 street	Oslo
10029		Rebecca		Ginny		4001		27 street	Manhattan
10027		Tobby		Riya		4002		31 street	Manhattan
10026		Sharma		Deepak		4002		10th street	New Delhi
10024		Krishna		Lina		4001		27 street	Oslo
10023		Jackson		David		4002		27 street	Manhattan
10022		Mayers		David		4003		27 street	Manhattan

Assume that a firm intends to close down its "sales and marketing" department. It wishes to have all employees in this department removed from the company's database. What method will you use to carry out this task? It's possible to do this by writing numerous sub-queries, but we'll just use a delete join statement.

Code:

DELETE t1
FROM employees AS t1 INNER JOIN department AS t2
ON t1.departmentid = t2.departmentid
WHERE t2.departmentname = 'Sales & Marketing';

Example 2: DELETE all students who are in a classroom where the teacher id is 42:

Now this will DELETE the matching records from table a. Notice the SELECT line is now commented out.

DELETE a

--SELECT a.*
FROM tblStudent a
INNER JOIN tblClassroom b
	ON a.ClassroomId = b.Id
WHERE b.TeacherId = 42

You may rest assured that if you follow this example, you will not delete the entire table, but only the records that fit the inner join.

Example 3: MySQL DELETE JOIN with INNER JOIN example

For example, to delete rows from both T1 and T2 tables that meet a specified condition, you use the following statement:

Suppose, we have two tables t1 and t2 with the following structures and data:

DELETE t1,t2 FROM t1
        INNER JOIN
    t2 ON t2.ref = t1.id 
WHERE
    t1.id = 1;

Notice that you put table names T1 and T2 between the DELETE and FROM keywords. If you omit T1 table, the DELETE statement only deletes rows in T2 table. Similarly, if you omitT2 table, the DELETE statement will delete only rows in T1 table.

The expression T1.key = T2.key specifies the condition for matching rows between T1 andT2 tables that will be deleted.

The condition in the WHERE clause determine rows in the T1 and T2 that will be deleted.

To joining those tables with distinct values:

Syntax:

SQL distinct column using INNER JOIN with WHERE clause.

SELECT DISTINCT a.col1, a.col2
FROM tab1 a INNER JOIN tab2 b
ON a.col1 = b.col1
WHERE b.col2 in ('value1', 'value2')
ORDER BY a.col1

The addition of the second column, a.col2, in the SELECT causes a full table scan.

Example 1: DISTINCT element at the beginning of the statement and work from there, e.g.

SELECT DISTINCT brands.brand_id FROM brands 
INNER JOIN 
products ON brands.brand_id = products.brand_id WHERE products.stock > 0"

Remove the inner join and replace it with a WHERE statement that uses the match.

Example 2:

select distinct a.FirstName, a.LastName, v.District
from AddTbl a 
  inner join ValTbl v
  on a.LastName = v.LastName
order by a.FirstName;

Example 3: The technique for joining two more tables is nearly identical to the one we used earlier. All you have to do now is add the same two JOINs as previously. All you have to remember is that the JOINs must be written in the proper order. You can't use columns from tables that haven't been published yet while joining.

SELECT DISTINCT
  teacher.first_name,
  teacher.last_name.
  student.first_name,
  student.last_name
FROM teacher
JOIN course
  ON teacher.id = course.teacher_id
JOIN student_course
  ON student.id = student_course.student_id
JOIN student
  ON student_course.course_id = course.id;
JOIN student_course
  ON course.id = student_course.student_id
JOIN student
  ON student_course.course_id = student.id;

I copied some code from the first query, which connected three tables, into the crossed-out section. The code was incorrect in this example; even if the requirements were correct, we were using tables that had not yet been introduced. For example, we utilised the student table, which was introduced later, to join the student_course database.

The right JOIN order may be seen below the crossed out code. The student_course and course tables are joined first. Then we may link the student database with the student_course table. This method, each table is introduced before being used in a JOIN... ON condition. Keep this vital rule in mind at all times!

Output:

first_name	last_name	first_name	last_name
Taylah		Booker		Shreya		Bain
Taylah		Booker		Rianna		Foster
Taylah		Booker		Yosef		Naylor
Sarah-Louise	Blake		Shreya		Bain
Sarah-Louise	Blake		Rianna		Foster

In this case, we’ve used an INNER JOIN. This means that if the teacher doesn’t have any students, they won’t appear in the results. Of course, you can replace the INNER JOIN with any other JOIN type, e.g., LEFT OUTER JOIN.

Only the rows that have a match in both connected tables are displayed in this join. In the joining table, distinct is utilised to discover a unique value.

When creating an inner join, columns containing NULL do not match any values and are hence removed from the result set. Null values do not match the null values of other null values.

A column's NULL values are never matched to other NULL values. This is because NULL denotes the absence of any value and can't be compared because they'll never be equal. When you say NULL = NULL, you get False.

Syntax:

SELECT field1, field2, field3
  FROM first_table
  JOIN second_table ON first_table.pk = second_table.fk

Example 1: Consider the animation provided in the exercise, which demonstrates how INNER JOIN works. Assume that each table has an additional entry with NULL in the C2 column, resulting in the tables becoming :

Left table:

C1, C2
A, B
Q, W
X, Y
T, NULL

Right table:

C2, C3
B, C
E, R
Y, Z
NULL, V

Because NULL values are not matched, if we inner connected these tables the same way, we'd get the same result.

C1, C2, C3
A, B, C
X, Y, Z

Example 2:

SELECT titles.title_id AS title_id,
       titles.title AS title,
       sales.qty AS qty
  FROM titles
  JOIN sales ON titles.title_id = sales.title_id
  ORDER BY title

EXISTS is way quicker than an inner join on a recordset with DISTINCT applied (to get rid of duplicates). IN and EXISTS clauses (with an equijoin correlation) typically use one of the numerous SEMI JOIN algorithms, which are usually faster than a DISTINCT on one of the tables.

INNER JOIN and EXISTS are two different things in general.

The former returns duplicates and columns from both tables, but the latter returns only one record and returns records from only one table because it is a predicate.

When you conduct an inner join on a UNIQUE column, the results are the same.

EXISTS is usually faster when doing an inner join on a recordset with DISTINCT applied (to remove duplicates).

Example 1:

TABLE1

ID (PK) Name
1001 Sam

TABLE2

IDadd (FK) Country
1001 USA
1001 UK

My query:

SELECT A.Name, B.Country
FROM TABLE1 A
INNER JOIN (SELECT TOP 1 * FROM TABLE2 WHERE IDadd = A.ID) AS B
ON B.IDadd = A.ID

Basically, the query intends to get one of the records from TABLE2 and join it to TABLE1.

Example 2:

SELECT p2.Name,
Categories.Name AS Expr1
FROM Categories
INNER JOIN (SELECT TOP (1) p1.Id, p1.Name from Products p1) AS p2
ON Categories.Id = p2.Id

If Categories to Products is a one to many relationship then, may be even this would be sufficient:

SELECT TOP (1) Products.Name,
Categories.Name AS Expr1
FROM Categories
INNER JOIN Products
ON Categories.Id = Products.Id

The GROUP BY clause can also be used with the Inner Join to group the tables.

Example 1: The following statement returns student id, technology name, city, and institute name using the Inner Join clause with the GROUP BY clause.

SELECT students.student_id, 
technologies.inst_name, students.city, technologies.technology    
FROM students   
INNER JOIN technologies    
ON students.student_id = technologies.tech_id GROUP BY inst_name;  

Example 2: Using GROUP BY with Join:

postgres=# SELECT count(e.isbn) AS "number of books",
postgres-#    p.name AS publisher
postgres-#    FROM editions AS e INNER JOIN publishers AS p
postgres-#    ON (e.publisher_id = p.id)
postgres-#    GROUP BY p.name;

Output:

number of books | publisher
-----------------+------------
               1 | B
               2 | D Inc
               1 | O Inc.
               2 | A Inc
               1 | H Pubs
               1 | W Pubs
               1 | P Inc
               2 | Z Inc
               1 | Can Press
               3 | R Inc
               1 | M Press
               1 | Henry Inc.

Example 3: To get the department name, you join the employees table with the departments table as follows:

SELECT e.department_id,
	department_name,
	COUNT(employee_id) headcount
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id;

To group the result set by one or more columns, we can use the SQL Inner Join Group by clause in conjunction with aggregate functions. On the final result produced after merging two or more tables, Group by works typically with Inner Join.

Rather to writing out the complete column identification, GROUP BY and/or ORDER BY the numeric column position in your SELECT statement.

Example 1: Use the ORDER BY clause with INNER JOIN to sort the results in ascending or descending order, as illustrated in this example. The query returns a list of records sorted by employee salary from highest to lowest:

For that, the SUM aggregate function is used in the SELECT statement and results are “GROUPED BY” employee names. For making sure only those employees records are retrieved that exist in the sto_orders table, the INNER JOIN clause is used as follows:

SELECT emp_name, SUM(emp_salary) As "Total Salary" FROM sto_employees EMP
INNER JOIN sto_orders ORD  ON EMP.id=ORD.emp_id
GROUP BY emp_name;
ORDER BY emp_salary DESC;

Example 2: For a decade (or more), I’ve specified column identifiers in my queries when grouping and/or ordering, like so:

SELECT extract(year FROM b.completed_at) AS yyyy,
    g.name,
    count(b.book_id) AS the_count
FROM
    book AS b
    INNER JOIN genre AS g ON b.genre_id = g.genre_id
GROUP BY
    yyyy, g.name
ORDER BY
    yyyy DESC, g.name

In the above example, the query results are grouped by yyyy, g.name, and the ordering notation is similar. and this can also be expressed using positional identifiers, like so:

SELECT extract(year FROM b.completed_at) AS yyyy,
    g.name,
    count(b.book_id) AS the_count
FROM
    book AS b
    INNER JOIN genre AS g ON b.genre_id = g.genre_id
GROUP BY
    1, 2
ORDER BY
    1 DESC, 2

The 1 and 2 in the GROUP BY indicate the column position in the SELECT. If you run Example 1 and Example 2, you’ll see the results are the same.

Example 1: Lets consider the two tables:

Table1

Name         Year    x      y
John Smith   2010    10     12
Adam Jones   2010    8      13
John Smith   2011    7      15
Adam Jones   2011    9      14

Table2

Name                    Year  z    
Smith John Smith John   2010  27
Jones Adam Jones Adam   2010  25
Smith John Smith John   2011  29
Jones Adam Jones Adam   2011  21

Basically, the names in Table2 are the same only with the last name and first name switched, then repeated once. So the Names in Table1 are found in the names of Table2 ("John Smith" is found in "Smith John Smith John"). I want to perform an inner join and connect the z value of Table2 to the other values of Table1 and get something like this:

Name       x     y     z
John Smith 10    12    27
Adam Jones 8     13    25

So to do that, I ran this query:

Select Table1.*, Table2.z
From Table1
Inner join Table2
On Table1.Name like "%Table2.Name%" and Table1.Year=Table2.Year

Output:

Name  Year  x  y  z

Example 2:

Use LIKE operator with INNER JOIN in SQL Server example:

"SELECT p.Pid,p.Title,p.Technology, 
p.Abstract, p.keywords, 
s.StudentName from Project p join Student s on  
p.Reg#=s.Reg#  WHERE Supervisor like  '" + TextBox1.Text + "%'  ";

If you want to use more than one column from the two tables in a join, you must include those columns in your ON clause, including the AND operator.

Syntax:

SELECT ...
FROM table1 INNER JOIN table2
ON table1.column1 = table2.column1
AND table1.column2 = table2.column2;

For example, the following query rewrites the previous example with the USING clause:

SELECT ...
FROM table1 INNER JOIN table2
USING (column1, column2);

Example 1: SQL INNER JOIN multiple columns:

SELECT TR1.Title AS SourceTitle, 
TR2.Title AS DestinationTitle
FROM [Result Map] AS RM
INNER JOIN Table_Result TR1 ON RM.Source_Id=TR1.Id
INNER JOIN Table_Result TR2 ON tr2.Id=RM.destination_id;

Output:

SourceTitle DestinationTitle
ABC         DEF  

Example 2: use Inner Join to join on multiple columns.

SELECT E.CaseNum, E.FileNum, E.ActivityNum, E.Grade, V.Score from Evaluation E
INNER JOIN Value V
ON E.CaseNum = V.CaseNum AND
    E.FileNum = V.FileNum AND 
    E.ActivityNum = V.ActivityNum

A SELF JOIN is an INNER JOIN or an LEFT OUTER JOIN (also known as LEFT JOIN) performed on the same or distinct columns or tables. It performs the same functions as the original, with the exception that the logic and procedures are applied to the same table and its replica.

When using self-join, you'll need to create a table alias to give the table a distinct name. The self-join only involves one table.

The goal of such a JOIN is to compare records in a table that would otherwise be impossible to do. Essentially, it's the same as making a duplicate of a table such that the table and its copy can interact as two separate tables, with each record in the table compared to its replica and all other entries in the table copy.

Syntax:

Depending on whether we utilise an INNER JOIN or an LEFT JOIN for the goal, the basic syntax of SELF JOIN can be either of the following.

SELECT column_list
FROM table1 alias1
INNER JOIN
table1 alias2
ON alias1.columnX=alias2.columnX;

Example 1: SQL Server SELF JOIN – hierarchical data processing with INNER JOIN:

There is a hierarchical link between employee and manager in the table above. The table's MgrId column is a subset of the Id column and contains data values that are similar to those in the Id column. This hierarchical relationship can be identified and reported via a SELF JOIN. The following query does this by using an INNER JOIN on two different table fields to create a SELF JOIN on the table (i.e. MgrId and Id columns as mentioned above). The output is sorted by the column alias 'Manager Name' to aggregate the output for simple comprehension, and the table is aliased first as e (i.e. the left table) and subsequently as m (i.e. the right table).

SELECT e.name 'Employee Name',
m.name 'Manager Name'
FROM emp e
INNER join emp m
ON e.mgrid = m.id
ORDER BY 'Manager Name';

It will generate the following output, which lists each employee's name along with his or her manager's name. However, if we look attentively, we can see that the three employees are the same (i.e. those who are managers and self-managed are missing from the list). Because an INNER JOIN only displays intersections (when the JOIN requirement is met or the connected column values in both tables match), this is the case.

Output:

Employee Name	Manager Name
Kirsten Rose	Albert Spencer
Korbin Miles	Albert Spencer
Robert Fox	Albert Spencer
Simon Ray	Julia Wells
Sandra Bull	Julia Wells
Steven Pitt	Raymond Cage
Jane Frost	Raymond Cage

Example 2:

To attach the employees to itself, the statement used the INNER JOIN clause. Employees and managers are represented at the employees table.

Employee data, as well as organisational data, are stored in the employees table. Employees' reporting relationships are specified in the ReportsTo column.

When an employee reports to a manager, the employee's ReportsTo column equals the manager's EmployeeId column. The ReportsTo field is NULL if an employee does not report to anyone.

To get the information on who is the direct report of whom, you use the following statement:

SELECT m.firstname || ' ' || m.lastname AS 'Manager',
e.firstname || ' ' || e.lastname AS 'Direct report' 
FROM employees e
INNER JOIN employees m ON m.employeeid = e.reportsto
ORDER BY manager;

The result set does not contain the entry whose manager column contains a NULL value since the INNER JOIN clause was used to join the employees table to it's own.

The concatenation operator || joins multiple strings together to form a single string. The concatenation operator is used in this example to create the full names of the employees by concatenating the first name, space, and last name.

If you want to query the CEO who does not report to anyone, modify the INNER JOIN clause in the query above to the LEFT JOIN clause.

Example 1: To sort the departments by headcount, you add an ORDER BY clause as the following statement:

SELECT e.department_id,
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY e.department_id
ORDER BY headcount DESC;

Example 2: We have already created two tables named students and technologies. Let us create one more table and name it as a contact.

SELECT student_id, inst_name, 
city, technology, cellphone  
FROM students   
INNER JOIN technologies USING (student_id)  
INNER JOIN contact ORDER BY student_id;  

The subquery used to join the query which is build inside another query.

Syntax:

SELECT * 
FROM
( SELECT ... FROM ... ) q1

INNER JOIN

( SELECT ... FROM ... ) q2

ON q1.colname = q2.colname

Example: INNER JOIN with Subquery

SELECT E.EID,DT.Salary FROM #Employees E
OUTER APPLY
(
    SELECT (SR.Rate * AD.DaysAttended) Salary
    FROM #SalaryRate SR
    INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
    WHERE SR.EID=E.EID
) DT; --Derived Table for outer apply

SELECT E.EID,DT.Salary FROM #Employees E
LEFT JOIN
(
    SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
    FROM #SalaryRate SR
    INNER JOIN #AttendanceDetails AD on AD.EID=SR.EID
) DT --Derived Table for inner join
ON DT.EID=E.EID;

The SQL SUM and SQL COUNT functions, together with the GROUP BY clause, create a SQL INNER JOIN statement. A temporary table or alias can be used to hold the results of a subquery.

Example 1: Sum of employee salary with join the table using sql aggregation

Select EMpNAME,AMT,DEPTNAME 
from (SELECT tbl_employee.E_Name as EMpNAME,sum(tbl_Salary.Amt) as AMT,
tbl_Dpt.DeptName as DEPTNAME
FROM tbl_employee 
INNER JOIN tbl_Salary ON tbl_employee.eid = tbl_Salary.eid 
INNER JOIN tbl_Dpt ON tbl_Salary.eid = tbl_Dpt.eid 
WHERE tbl_employee.eid ='E101'
GROUP BY tbl_employee.E_Name,tbl_Dpt.DeptName) as A

Example 2: SQL INNER JOIN with SUM:

SELECT Sum(Order.Amt) AS SC
FROM Order INNER JOIN Cust ON Order.ID=Cust.ID
WHERE Order.Type='Fin' AND Cust.State ='WA'

Example 3:

SELECT ag.agent_code, ag.agent_name,cus.mycount,cus.mySUM
FROM agents ag
INNER JOIN (
SELECT agent_code,COUNT(*) AS mycount,
SUM(opening_amt) AS mySUM
FROM Customer
GROUP BY agent_code) cus
ON cus.agent_code=ag.agent_code;

Inner join is used to join tables; use the union clause to join inner join queries.

Syntax:

select *
from 
(
    select some_table1.some_column1, some_table1.some_column2
    FROM some_table1
    UNION ALL
    select some_table2.some_column1, some_table2.some_column2
    FROM some_table2
) t1
INNER JOIN some_table3
ON  some_table3.some_column1 = t1.some_column1 

Example: SQL UNION with Inner Join:

The union created by two queries is shown in the following example. Two inner join statements make up the queries. In the first query, the join occurs between two tables with the same prod_code, while in the second query, the join occurs between two tables with the same prod_name.

SQL Code:

SELECT product.prod_code,product.prod_name,
purchase.pur_qty, purchase.pur_amount  
FROM product
INNER JOIN purchase  
ON product.prod_code =purchase.prod_code

UNION

SELECT product.prod_code,product.prod_name,
purchase.pur_qty, purchase.pur_amount  
FROM product
INNER JOIN purchase  
ON product.prod_name =purchase.prod_name;

Output:

PROD_CODE  PROD_NAME          PUR_QTY PUR_AMOUNT
---------- --------------- ---------- ----------
PR001      T.V.                    15     450000
PR002      DVD PLAYER              10      30000
PR003      IPOD                    20      60000
PR004      SOUND SYSTEM             8      40000
PR005      MOBILE                 100     300000

SQL UPDATE JOIN can be used to update a column in a table using a join condition from another table.

You can achieve this in SQL Server by connecting tables together with the UPDATE command.

Join clauses, either inner join or left join, are frequently used to query data from related tables. To do a cross-table update, utilise these join clauses in the UPDATE statement.

Syntax:

The following illustrates the syntax of the UPDATE JOIN clause:

UPDATE table1
SET table1.column = table2.expression1
FROM table1
INNER JOIN table2
ON (table1.column1 = table2.column1)
[WHERE conditions];

Example 1: can use UPDATE statement as below:

UPDATE CUST
SET    CUST.OrderAmount = ORDR.Amount
FROM   dbo.Customers CUST
INNER JOIN dbo.Orders ORDR ON CUST.CustomerID = ORDR.CustomerID
GO

OrderAmount column in dbo.Customers Table is now updated based on JOIN condition.

SELECT CustomerID, Name, OrderAmount
FROM   dbo.Customers
GO

Output:

CustomerID    Name          OrderAmount
3             Curtis        5693.00
4             Lanna         NULL
5             Marlin        4582.00
6             Henry         8745.00

Example 2: For example:

UPDATE employees
SET employees.first_name = contacts.first_name
FROM employees
INNER JOIN contacts
ON (employees.last_name = contacts.last_name)
WHERE employee_id > 95;

Example 3: SQL Server UPDATE INNER JOIN example:

The following statement uses the UPDATE INNER JOIN to calculate the sales commission for all sales staffs:

UPDATE
    sales.commissions
SET
    sales.commissions.commission = 
        c.base_amount * t.percentage
FROM 
  sales.commissions c
  INNER JOIN sales.targets t
  ON c.target_id = t.target_id;

Use INNER JOIN to return just entries with a pair on both sides, and LEFT JOIN to return all records from the "left" table, regardless of whether or not they have a pair in the "right" table.

The accuracy of both INNER and LEFT JOINS is dependent on the number of JOINS performed and whether or not the columns are indexed. Performing entire 9 to 10 table scans for each JOIN could also slow down the operation. LEFT JOIN may be quicker than INNER JOIN when some of the tables are very small, say under 10 rows, or when the tables do not have adequate indexes to cover the query. As a result, the situations are crucial.

INNER JOIN

Inner join returns only the common records that match in two tables that have been joined; non-matching rows are removed.

Only rows that match an ON condition are selected when using INNER JOIN. There will be no results displayed if there are no rows that match the ON requirement.

Example: Here in the above example we have “ClassID” in tclass table and “ClassID” in tstudent table. Below query is used to fetch the list of students which are in Class “10th”:

SELECT s.StudentID, s.StudentName
FROM tClass c 
INNER JOIN tstudent s ON c.ClassID = s.ClassID
WHERE c.ClassID = 10

As a result, the aforementioned query is used to retrieve a list of students in Class – "10th." As a result, all students are saved in the "tstudent" table, regardless of their classes, and all classes are recorded in the "tclass" table. As a consequence of the query above, matched records from the tstudent and tclass tables are found and shown. The term "predicate" was introduced in the introductory section; in the above query, it is "ON c.ClassID = s.ClassID," which is an important aspect of the join.

Note: Each table has several keys, for example, the tStudent table has "StudentID" as a primary key field and "ClassID" as a foreign key field, both of which relate to the table – "tClass."

The above query can be rewritten without using inner join like below but the performance will be impacted compared to inner join :

SELECT s.StudentID, s.StudentName
FROM tClass c , tstudent s
WHERE c.ClassID = s.ClassID AND c.ClassID = 10

LEFT JOIN

Because the data returned with either of these will be the same, plus non-matching rows from the left table, the terms Left Join and Left Outer Join are comparable.

All rows from the left table and matching rows from the right table will be returned by a left join.

It will return NULL if there is no match with the right side table.

LEFT JOIN returns all of the data from the first table, regardless of whether or not there are any matches with the second table, and NULL is used to fill in the gaps.

Example: Below query is used to fetch the all the classes and the students are in that class.

SELECT s.StudentID, s.StudentName
FROM tClass c 
LEFT JOIN tstudent s ON c.ClassID = s.ClassID

Above can be rewritten using “LEFT OUTER JOIN” as:

SELECT s.StudentID, s.StudentName
FROM tClass c 
LEFT OUTER JOIN tstudent s ON c.ClassID = s.ClassID

As illustrated in the accompanying diagram, all rows from "tclass" are retrieved, as are the students of the various classes. If no students are identified in that class, the class will still be retrieved from the left table, with NULL displayed in place of "StudentID" and "StudentName."

Main Article :- Sql difference between INNER JOIN and OUTER JOIN

SQL inner and outer joins use a join condition to aggregate rows from many tables into a single result. The join condition determines how each table's columns are matched. Most of the time, the goal is to discover tables with similar values and include those that match.

Even if the join condition fails, the outer join will return every row from one selected table, unlike an inner join.

Inner Join

• An inner join looks for data that matches or overlaps between tables. When it finds it, the inner join joins the data together and returns it to a new table.
• When you're comparing the foreign key of one table to the primary key of another, such as when using an ID to look up a value, this is the most usual scenario.
• The clause used is ‘INNER JOIN’ and ‘JOIN’.
• Inner joins combine rows from two or more tables to deliver results. Inner joins only return rows where the match condition is true.
• The result is empty if no characteristics are shared.
• In compared to 'OUTER JOIN,' 'INNER JOIN' works faster when the number of tuples is greater.
• It's utilised when you need a lot of information on a certain property.
• The 'JOIN' and 'INNER JOIN' functions are identical.

Syntax:

SELECT *
FROM table_1 INNER JOIN / JOIN table_2
ON table_1.column_name = table_2.column_name;

Example 1: Suppose the HR Manager wants to create a phone directory. They want the person’s first name, last name, title, and phone numbers. What query could you use to create this :

SELECT   P.FirstName,
         P.LastName,
         P.Title,
         PH.PhoneNumber
FROM     Person.Person AS P
         INNER JOIN
         Person.PersonPhone AS PH
         ON P.BusinessEntityID = PH.BusinessEntityID
         AND PH.PhoneNumberTypeID = 3
ORDER BY P.LastName

The INNER JOIN defines the tables to join as well as the matching condition. The PH.Phone NumberTyeID = 3 condition restricts the query to work numbers.

The rows where the BusinessEntityIDs don't match aren't included in this example. If a person does not have a phone number, this might be a problem because those employees would not be on the list.

Example 2: For example, if we have two tables, one for product price and the other for product quantity, the common field in both tables will be Product ID or Product Name, and the tables will logically be connected based on this column. Some products will be common in both tables, while others may not. Only the common products of both tables will be returned if an inner join is done on both tables.

PRODUCT PRICE and TABLE PRICES

PRODUCT		 PRICE
Potato		$4
Kiwis		$3
Melons		$2
Oranges		$5
Tomatoes	$4
Avocado		$6

PRODUCT QUANTITY and TABLE QUANTITIES

PRODUCT	QUANTITY
Potato	221
Broccoli	23
Squash	55
Melon	45
Kiwi	67
Avocado	87

Query:

SELECT PRICES.*, QUANTITIES.QUANTITY 
FROM PRICE 
INNER JOIN QUANTITIES ON PRICE.PRODUCT=QUANTITIES.PRODUCT; 

Output:

PRODUCT	PRICE	QUANTITY 	 	 
POTATO	$4	22
KIWI	$3	67
MELON	$2	45
AVOCADO	$6	87

Outer Join

The rows returned by an outside join are the same as those returned by an inner join, but they also include rows for which no corresponding match in the other table can be identified.

There are three types of outer joins:

• Left Outer Join (or Left Join)
• Right Outer Join (or Right Join)
• Full Outer Join (or Full Join)

The component of the data that is compared, aggregated, and returned is referred to by each of these outer joins. Because some data is exchanged while other data is not, nulls may be produced throughout this transaction.

Outer Join Rules:

• It returns the combined tuple of a specified table.
• An outer join is used to return results by combining rows from two or more tables.
• It is returned even when the ‘JOIN’ condition fails.
• It doesn’t depend on the common attributes.
• If the attribute is blank, NULL is placed instead of the blank.
• The ‘OUTER JOIN’ is slow in comparison to ‘INNER JOIN’.
• It is used when complete information is required.
• FULL OUTER JOIN and FULL JOIN clauses work in the same manner.

Syntax:

SELECT *
FROM table_1 LEFT OUTER JOIN / RIGHT OUTER JOIN /
FULL OUTER JOIN / FULL JOIN table_2 ON
Table_1.column_name = table_2.column_name;

Example 1: Take the phone directory example above. If the HR manager wanted to list every employee regardless of whether they had a work phone number, then using an outer join would make it so.

SELECT   P.FirstName,
         P.LastName,
         P.Title,
         PH.PhoneNumber
FROM     Person.Person AS P
         LEFT OUTER JOIN
         Person.PersonPhone AS PH
         ON P.BusinessEntityID = PH.BusinessEntityID
         AND PH.PhoneNumberTypeID = 3
ORDER BY P.LastName

For now, just know that when an LEFT OUTER JOIN is used, all rows for the table in the FROM clause are included in the result, even if a match with the other table isn't discovered.

Inner join: We can choose fields from both tables using an inner join.

Because it only takes one trip across the data, employing an INNER JOIN may be more efficient.

Example 1:

SELECT E.ntEmpID,
    E.vcName,
    E.vcName,
    ED.moSalary,
    ED.dtDOJ
FROM tblEmp E INNER JOIN tblEmpDetail ED
ON E.ntEmpID = ED.ntEmpID

Example 2:

SELECT   E.HireDate,
         P.FirstName,
         P.LastName,
         E.BirthDate
FROM     HumanResources.Employee E
         INNER JOIN Person.Person P
         ON P.BusinessEntityID = E.BusinessEntityID
WHERE    P.PersonType  = 'EM'
ORDER BY E.HireDate,
         P.FirstName

Subquery: we can get the data from the only table.

correlated sub-query must execute for each row in the outer query.

Example:

SELECT E.ntEmpID,
    E.vcName,
    E.vcName
FROM tblEmp E
WHERE ntEmpID IN (SELECT ntEmpID
    FROM tblEmpDetail)

In this case, we can't use a select list to include fields from the table tblEmpDetail. So, if we need data from both tables' fields, the only option is to connect them. The subquery does not allow it.

The FROM or WHERE clauses can be used to specify inner joins. Only the FROM clause can provide outer joins and cross joins. The join conditions work together with the WHERE and HAVING search criteria to determine which rows are selected from the FROM clause's basis tables.

The equal operator (=) was used in the join condition to match rows. Other operators, such as larger than (>), less than (<), and not-equal (<>), can be used to establish the join condition in addition to the equal operator (=).

It is not mandatory to use the INNER JOIN phrase. You can use the WHERE clause to achieve the same result.

• WHERE CLAUSE can also be used with INNER JOIN, just as it can be with LEFT JOIN and ON Clause.
• In a SELECT Query on a single or more tables, the WHERE CLAUSE is used to filter the rows.
• After performing INNER JOIN JOIN, INENR JOIN performs the same filtering function.
• The WHERE CLAUSE can be used on either Table1 or Table2 in the INNER JOIN.

Syntax:

Here is the syntax of how you can use WHERE CLAUSE with SQL INENR JOIN like in SQL LEFT JOIN.

SELECT columns_name(s)
FROM Table1
INENR JOIN Table2
ON 
Join_predicate
WHERE_CLAUSE predicate;

WHERE CLAUSE in SQL is used to find matches between two tables in certain situations.

Example 1: Here is our example 3 for the use of WHERE Clause:

SELECT Courses.CID, Courses.CourseName, 
CourseRegistration.StudentID, CourseRegistration.Semester, 
Courses.CR, Courses.Duration, CourseRegistration.TeacherID, 
CourseRegistration.CourseID
FROM Courses 
INNER JOIN CourseRegistration 
ON Courses.CID = CourseRegistration.CourseID
WHERE (((CourseRegistration.Semester)="Spring"));

The Total number of rows specifies the difference between the OUTPUT Table 3 and the preceding instance in this WHERE Clause and INNER JOIN Example. The values of the First Column (CID) come from the Courses table, whereas the values of the Last Column (CourseID) come from the Courses Registration table.

Example 2: Inner Join using WHERE Clause:

SELECT emp.EmpId, emp.FirstName, emp.LastName, dept.Name 
FROM  Department dept, Employee emp
WHERE dept.DeptId = emp.DeptId;

Example 3: The following example illustrates this clause with Inner Join:

SELECT tech_id, inst_name, city, technology    
FROM students   
INNER JOIN technologies    
USING (student_id) WHERE technology = "Java";  

Example 4: The following is a simple SELECT statement using this join:

SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name
FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor
    ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID)
WHERE StandardPrice > $10
  AND Name LIKE N'F%'
GO

The SELECT statement returns product and supplier information for any combination of parts supplied by a company whose name begins with the letter F and the product cost exceeds $10.

Example 5:

The less-than (<) join is used in the following query to determine the sales price of the product with the code S10 1678 that is less than the manufacturer's suggested retail price (MSRP).

SELECT orderNumber, 
    productName, 
    msrp, 
    priceEach
FROM
    products p
INNER JOIN orderdetails o 
   ON p.productcode = o.productcode
      AND p.msrp > o.priceEach
WHERE
    p.productcode = 'S10_1678';