For an instructor lead, in-depth look at learning SQL click below.
The SQL INNER JOIN keyword is one of the most critical commands in data manipulation and analysis, allowing you to combine rows from two or more tables based on a related column between them. Its role is central to most data operations, from straightforward data retrieval tasks to intricate data mining processes.
Understanding INNER JOIN
The INNER JOIN keyword selects records with matching values in both tables. It compares each row of the first table with each row of the second table to find all pairs of rows which satisfy the join condition. The result set of the SQL INNER JOIN contains only such matching pairs.
To illustrate this further, let’s work with some sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/* Creating tables */ CREATE TABLE Orders ( OrderID int, CustomerID int, OrderAmount float ); CREATE TABLE Customers ( CustomerID int, Name varchar(255), City varchar(255) ); /* Inserting data into Orders */ INSERT INTO Orders VALUES (1, 1, 19.99), (2, 2, 29.99), (3, 3, 39.99); /* Inserting data into Customers */ INSERT INTO Customers VALUES (1, 'John Doe', 'New York'), (2, 'Jane Doe', 'Los Angeles'), (3, 'Jim Brown', 'Chicago'); |
Basic INNER JOIN Syntax
The basic syntax for INNER JOIN is as follows:
|
1 2 3 4 5 6 |
SELECT column_name(s) FROM first_table INNER JOIN second_table ON first_table.common_field = second_table.common_field; |
For instance, if we’d like to join our Orders and Customers tables based on the common field ‘CustomerID’, we’d write the following SQL command:
|
1 2 3 4 5 6 |
SELECT Orders.OrderID, Customers.Name FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; |
Output:
|
1 2 3 4 5 6 |
OrderID Name 1 John Doe 2 Jane Doe 3 Jim Brown |
Conclusion
With INNER JOIN, you have a versatile tool to combine and analyze disparate yet related datasets. Now that you are familiar with the basic application of INNER JOIN, you can discover more advanced usage options, including methods to deal with non-matching records and multiple join operations. Happy SQL coding!
|
1 2 3 |