Inner join is used to select rows from multiple tables based on a matching column in one or more tables. It compares each row value of a table with each row value of another table to find equal values. If equal value are found in multiple columns from multiple tables, they are returned in the result set.
Basic Inner Join Syntax
SELECT T1.Columns, T2. Columns FROM Table1 as T1 INNER JOIN Table2 T2 ON T1.Pk_Table1_Rowid = T2.Table2_Rowid;
Inner Join Example
IF OBJECT_ID ('Table1', 'U') IS NOT NULL DROP TABLE Table1 CREATE TABLE Table1 ( Id INT, FruitName Varchar( 100) ); IF OBJECT_ID ('Table2', 'U') IS NOT NULL DROP TABLE Table2 CREATE TABLE Table2 ( Id INT, FruitName Varchar( 100) ); Insert into Table1 Values ( 1,'Mango' ), ( 2, 'Graps'), (3, 'Banana'); Insert into Table2 Values ( 1,'Mango' ), ( 2, 'Graps'), (4, 'Orange'); SELECT * FROM Table1 ; SELECT * FROM Table2 ; SELECT * FROM Table1 T1 Inner Join Table2 T2 ON T1. Id = T2 .Id;
Update Using Inner Join in SQL Server
Using the same concept of Inner join, we can update rows in one table based on another table using Inner Join.
Syntax for Update with Inner Join
UPDATE T2 SET T2. Name = T1 .Name FROM Table2 as T2 INNER JOIN Table1 as T1 ON T1. Id = T1 .Id;
To simplify syntax, T2 is an alias name for Table2, whose rows we want to update based on matching rows with Table1. On clause specifies the column names to find matching rows between both tables using Inner Join. SET specifies the Table2 column Name will be updated with values of Table1 name column.
Update with Inner Join Example
IF OBJECT_ID ( 'Table1', 'U' ) IS NOT NULL DROP TABLE Table1 CREATE TABLE Table1 ( Id INT, FruitName Varchar( 100 ) ); IF OBJECT_ID ( 'Table2', 'U' ) IS NOT NULL DROP TABLE Table2 CREATE TABLE Table2 ( Id INT, FruitName Varchar( 100 ) ); Insert into Table1 Values ( 1 ,'Mango' ), ( 2 , 'Graps' ), ( 3, 'Banana'); Insert into Table2 Values ( 1 ,'Mango' ), ( 2 , NULL ), ( 3, NULL ); SELECT * FROM Table1 ; SELECT * FROM Table2 ; UPDATE T2 SET T2. FruitName = T1 .FruitName FROM Table2 as T2 INNER JOIN Table1 as T1 ON T1. Id = T2 .Id; SELECT * FROM Table1 ; SELECT * FROM Table2 ;
In the example above, NULL values rows in Table2 are updated with Table1 rows based on a matching ID column.
Before Update with Inner Join
After Update with Inner Join
Delete Using Inner Join
Using the same concept of Inner join, we can delete rows from one table based on another table using Inner Join.
Syntax for Delete with Inner Join
DELETE T2 FROM Table2 as T2 INNER JOIN Table1 as T1 ON T1. Id = T1 .Id;
To simplify syntax, T2 is an alias name for Table2, whose rows we want to delete based on matching rows with Table1. On clause specifies columns names to find matching rows between both tables using Inner Join.
IF OBJECT_ID ( 'Table1', 'U' ) IS NOT NULL DROP TABLE Table1 CREATE TABLE Table1 ( Id INT, FruitName Varchar( 100 ) ); IF OBJECT_ID ( 'Table2', 'U' ) IS NOT NULL DROP TABLE Table2 CREATE TABLE Table2 ( Id INT, FruitName Varchar( 100 ) ); Insert into Table1 Values ( 1 ,'Mango' ), ( 2 , 'Graps' ), ( 3, 'Banana'), (4, 'WaterMelon'), (5, 'Orange'); Insert into Table2 Values ( 1 ,'Mango' ), ( 2 , 'Graps'), (3 , 'Banana' ), ( 6, 'Pear'), (7, 'Papaya'); SELECT * FROM Table1 ; SELECT * FROM Table2 ; DELETE T2 FROM Table2 as T2 INNER JOIN Table1 as T1 ON T1. Id = T2 .Id; SELECT * FROM Table1 ; SELECT * FROM Table2 ;
In the example above, Rows with Id (1,2,3) are deleted from table T2 because it matches with Table1 (Id) Column with Inner join.
Before Delete with Inner Join
After Delete with Inner Join
Currently SQL server does not support deleting rows from both the tables using one delete statement like other RDBMS.
See also SQL Server Archives
