SQL - SPLessons

SQL Inner Joins

Chapter 37

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Inner Joins

SQL Inner Joins

shape Description

An inner join joins between two or more tables that fulfills the join cases. SQL Inner Joins utilize a correlation administrators like = or <> to coordinate from two tables in light of the qualities in like manner sections from every table. Before proceeding to inner joins the user need to learn about Joins,which provide the details explanation regarding joins.

shape Conceptual
figure

shape Syntax

The syntax for SQL Inner Joins is as follows:
Select table1.<columnname1>, table1.<columnname2>, table2.<columnname1>, table2.<columnname2>from table1 INNER JOIN table2 ON table1.<column>=table2.<column>;

Table 1 and table 2 => The accurate table names inside the database.

Column name => The operation that can be performed on the column by using SQL Inner Joins.

shape Examples

sql> create table customer(customer_id number(4),customer_name varchar2(20),city varchar2(20),country varchar2(20));
Query OK, 0 rows affected (0.49 sec)

sql> insert into customer values(1001,'James','Maria Anders','United states');
Query OK, 1 row affected (0.11 sec)

sql> insert into customer values(1002,'Mike','Trujillo','Germany');
Query OK, 1 row affected (0.09 sec)

sql> insert into customer values(1003,'Kate','Mataderos','France');
Query OK, 1 row affected (0.23 sec)

sql> select * from customer;
+-------------+---------------+--------------+---------------+
| customer_id | customer_name | city         | country       |
+-------------+---------------+--------------+---------------+
|        1001 | James         | Maria Anders | United states |
|        1002 | Mike          | Trujillo     | Germany       |
|        1003 | Kate          | Mataderos    | France        |
+-------------+---------------+--------------+---------------+
3 rows in set (0.00 sec)

sql> create table orders(order_id number(4),customer_id number(4),employee_id number(4),shipper_id number(4));
Query OK, 0 rows affected (0.36 sec)

sql> insert into orders values(1101,1002,1011,3);
Query OK, 1 row affected (0.11 sec)

sql> insert into orders values(1102,12,1012,5);
Query OK, 1 row affected (0.17 sec)

sql> insert into orders values(1103,21,1033,7);
Query OK, 1 row affected (0.11 sec)

sql> select * from orders;
+----------+-------------+-------------+------------+
| order_id | customer_id | employee_id | shipper_id |
+----------+-------------+-------------+------------+
|     1101 |        1002 |        1011 |          3 |
|     1102 |          12 |        1012 |          5 |
|     1103 |          21 |        1033 |          7 |
+----------+-------------+-------------+------------+
3 rows in set (0.00 sec)

sql> Select customer.customer_name,orders.order_id from customer INNER JOIN orders on customer.customer_id=orders.customer_id order by customer.customer_name;

+---------------+----------+
| customer_name | order_id |
+---------------+----------+
| Mike          |     1101 |
+---------------+----------+
1 row in set (0.14 sec)

sql> SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

+---------------+---------------+
| order_id      | customer_name |
+---------------+---------------+
|    1101       |      Mike     |
+---------------+---------------+
1 row in set (0.14 sec)

Summary

shape Key Points

  • SQL Inner Joins – Two or more tables will be joined that satisfy the given cases.