Join Sql Explained

Queries over several database tables are realized in the relational database model with the help of SQL JOINs. All JOIN types, with the exception of CROSS JOIN, are a combination of Cartesian product and selection. The database management system (DBMS) first forms the cross product of two database tables. It then filters the result according to a selection condition defined by the user via SQL statement. selection condition. The INNER JOIN is distinguished from all other JOIN types by a minimum result set from all other JOIN types. The result of an INNER JOIN is only the data records of the cross product that fulfill the selection condition. The result is a result table (a view) without null values. Table of contents

INNER JOINs in practice
Subtypes of the INNER JOIN
1. THETA JOINs, EQUI JOINs and NON EQUI JOINs
2. NATURAL JOINs

Domain Bundle Promotion Only for a short time: How to get two free domains! Buy a .de domain and get a .com and .info domain for free. E-mail box Wildcard SSL Personal consultant

INNER JOINs in practice

We will illustrate the INNER JOIN with an example and assume two tables. The table “mitarbeiter” contains the employees of a company including the employee ID (m_id) and the corresponding department (a_id) are recorded. Table: employees

m_id	last name	first name	a_id
1	Schmidt	Udo	3
2	miller	Wolfgang	1
3	Meyer	Günther	1
4	ruffle	Helmut	2
5	Schneider	Kevin	ZERO

The table shows two peculiarities: The employees Müller and Meyer work in the same department. The employee Schneider has not been assigned to any department yet. The table “departments” lists all departments of the company including ID and location. Table: departments

a_id	designation	location
1	sales department	Frankfurt
2	IT	Bad Homburg
3	Personnel	Offenbach
4	Research	Bad Homburg

Both tables are linked via a foreign key relationship with each other. The department ID, which is the primary key in the “departments” table, has been integrated into the “employees” table as a foreign key. This link allows us to perform an INNER JOIN across both tables. Such a join is necessary, for example, to determine which employee works at which location. When querying relational databases, we usually use a primary and foreign key is defined as a selection condition. is defined. The condition is considered to be met if the selected foreign key of one table matches (=) the primary key of the other table. Thus, only the records that contain common values are output. Such an INNER JOIN is used in relational algebra as follows. employee⋈_{a_id=a_id}departments However, relational database systems do not accept commands in the syntax of relational algebra, but in the form of SQL statements.

SELECT * FROM employees INNER JOIN departments ON employees.a_id = departments.a_id;

The command SELECT command instructs the DBMS to retrieve data from the database. Alternatively, SQL offers the possibility to enter (INSERT INTO), change (UPDATE) or delete (DELETE FROM) data. The SELECT command is followed by the specification of which data should be retrieved. Since we want to retrieve the complete data set, we choose an appropriate placeholder: the asterisk (*). The SELECT command always requires the keyword FROM and the specification from which table or table group (JOIN) the data is to be retrieved. In our case, the data source is an INNER JOIN over the tables “departments” and “employees”. In addition we specify with the keyword ON keyword to specify a condition for the join. We only want to join and output as a result table those records where the a_id of the table “employee” matches the a_id of the table “departments”. Tip Since the INNER JOIN is the most important SQL JOIN, you can also omit the “INNER” keyword if necessary. An INNER JOIN over the two output tables with the condition employees.a_id = departments.a_id returns the following result table. Table: SQL INNER JOIN over “employees” and “departments

m_id	last name	first name	employee.a_id	departments.a_id	name	location
1	Schmidt	Udo	3	3	Personnel	Offenbach
2	Müller	Wolfgang	1	1	Sales	Frankfurt
3	Meyer	Günther	1	1	Sales	Frankfurt
4	Krause	Helmut	2	2	IT	Bad Homburg

If you compare the results table with the two output tables, you will notice that one record from each table is missing. Namely the records for whose value in the column a_id there is no equivalent in the other table.

(5, Schneider, Kevin, NULL)

(4, Research, Bad Homburg)

The employee Schneider has not yet been assigned a department. No employees have yet been assigned to the Research department. Both data records are hidden in an INNER JOIN, which is used to match employees to their respective departments. If, on the other hand, we want to determine exactly such irregularities and make them visible in the context of the query, we should choose an OUTER JOIN instead of an INNER JOIN. An INNER JOIN as a set diagram: The result set of an INNER JOIN over tables A and B corresponds to the intersection of A and B.

Subtypes of the INNER JOIN

INNER JOINs can be realized as THETA JOINs, EQUI JOINs, NON EQUI JOINs and NATURAL JOINs.

THETA JOINs, EQUI JOINs and NON EQUI JOINs

The INNER JOIN of the SQL terminology corresponds to the THETA JOIN of the relational algebra. The THETA JOIN differs from EQUI JOINs and NON EQUI JOINs in that it provides users with an unrestricted set of comparison operators to choose from. EQUI JOINs on the other hand, restrict the selection condition for queries to the equality of column values. For NON EQUI JOINs all comparison operators are allowed except the equal sign.

JOIN type	Allowed comparison operators
THETA JOIN	= (equal to) < (less than) > (greater than)≤ (less than or equal to)≥ (greater than or equal to)<> (not equal to)!= (not equal to)
EQUI JOIN	= (equal)
NON EQUI JOIN	< (less than) > (greater than)≤ (less than or equal to)≥ (greater than or equal to)<> (unequal)!= (unequal)

NATURAL JOINs.

When two tables (as in the preceding examples) are joined by columns with the same name, INNER JOINs are usually called NATURAL JOINs are used. NATURAL JOINs are a subtype of the EQUI JOIN. Like the EQUI JOIN, the NATURAL JOIN requires the equality of two column values as a selection condition. A NATURAL INNER JOIN over the tables “employees” and “departments” could be realized as follows:

SELECT * FROM employees INNER JOIN departments USING(a_id);

The SQL statement instructs the DBMS to join the listed tables. The selection condition is implemented using the keyword USING, which specifies which columns are to be checked for equality. The prerequisite is that in both tables a column a_id exists in both tables. Records from both tables are included in the result set only if the DBMS finds identical values in the columns specified with a_id are included in the result set. The result table of the NATURAL JOIN also differs from that of the classic INNER JOIN in that columns of the source tables with the same name are not listed twice, but are merged into a common column. Table: NATURAL JOIN using “employees” and “departments

m_id	last name	first name	a_id	name	location
1	Schmidt	Udo	3	personnel	Offenbach
2	Müller	Wolfgang	1	Sales	Frankfurt
3	Meyer	Günther	1	Sales	Frankfurt
4	Krause	Helmut	2	IT	Bad Homburg

Instead of department IDs of both tables as employee.a_id and departments.a_id twice, only one column a_id is played. For NATURAL JOINs, a shorthand notation is available that does not require a USING clause. Instead, the NATURAL JOIN operator is used. The shorthand notation of the above operation corresponds to the following SQL statement.

SELECT * FROM employees NATURAL JOIN departments;

The NATURAL JOIN operator automatically joins tables using columns with the same name. Thus, the selection condition does not have to be defined explicitly. Note A NATURAL JOIN is automatically converted to an INNER JOIN. However, if you want to convert an OUTER JOIN as a NATURAL JOIN, additional keywords are required (for example, NATURAL LEFT OUTER JOIN). Related articles SQL-JOIN – Queries across multiple data tables A strength of the relational database model is the outsourcing of information to separate database tables structured according to semantic relationships. This concept, known as normalization, is the basis for keeping data as redundancy-free as possible, but it requires a mechanism for merging data from different tables for queries…. SQL-JOIN – Queries over multiple data tables Databases: Why you need them and what types there are Databases are a fundamental part of today’s information society. They organize electronic data and regulate access to it. Databases have very many applications, including both commercial and private. Each database system has a database management system based on a particular database model. While the history of… Databases: why you need them and what types there are SQL OUTER JOIN OUTER JOINs are special queries across multiple database tables. A distinction is made between LEFT OUTER JOINs, RIGHT OUTER JOINs and FULL OUTER JOINs. Each of these JOIN types is distinguished from the INNER JOIN by a larger result set. But how exactly do the individual types differ from each other? We distinguish OUTER JOINs from INNER JOINs,… SQL OUTER JOIN Join Sql Explained.