- 5 years ago
- Zaid Bin Khalid
- 2,355 Views
-
5
In this session, we will learn about how to apply SQL join operations and how to connect the two tables to view combined data results.
The SQL Join Fundamentals.
Most of the queries that have been going through so far focused on single tables of a database. However, in real-life situations, you might face queries that have two or more tables at a time and may have resulting sets of combined tables. Technically, this type of connecting table referred to as join. Though, it might involve the joining of different tables. Also, to create a new view of the data table, you might need Inner Join to create a relation of common field (foreign key) between them.
To understand join fundamentals, let’s have a look at the following mention below, employees and departments tables.
+--------+--------------+------------+---------+
| emp_id | emp_name | hire_date | dept_id |
+--------+--------------+------------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
+--------+--------------+------------+---------+
Table: employees
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
Table: departments
Here, the dept id columns of the employees’ table are the foreign key to the departments’ table. Therefore. These two table employees’ tables and department tables can join to get the combined data.
Note: To join employees table and department tables, the data of the columns used for joining tables must match. In the employees’ table and department tables, it is not necessarily the column names.
Here are a few types of Joins. Later on, we will talk about each type in detail.
The Types of Joins.
The type of join is essential, especially when you are joining the tables. Hence the type of join that one is creating might affect the rows that appear on the resulting sets. One can create the following types of join in their tables.
The Inner join.
The Inner join only returns the rows that contain match values on both joined tables. For instance, you can join both employees and department tables to create a set of results that depict the department name for each employee.
The Outer join.
The outer join is one of the extensions to inner joins. Typically, an outer join will return the rows, even after they don’t have related rows in their join tables. Interestingly, there are around three types of out joins that include left outer join (left join), full outer join (full join), or right outer join (right join).
The Cross join.
Cross joins are the type of joins that don’t apply a join condition. Each row among the table combines with each row of another table. You can name these types of result sets as a Cartesian product or cross product.
- 5 years ago
- Zaid Bin Khalid
- 2,355 Views
-
5