SQL PREPARATION TIME NOTE 4
In this article, You learn SQL Joins, Relationship, Mapping Cardinality, and Attribute. Joining is used in SQL when more than one table is related in a Query. Relationship stands for how entities and related to each other. Mapping cardinality stands for in which order entities are related. Attribute refers to which type of value is stored in a relationship.
The most common portions of SQL is already covered in SQL PREPARATION TIME NOTE 1, SQL PREPARATION TIME NOTE 2, and SQL PREPARATION TIME NOTE 3. In those articles, You can get your confidence over SQL commands.
PREPARATION for LIFETIME
As discussed earlier, the Teacher and Teacher_Accounts table is used to demonstrate how Joining operations are done.
Teacher and Teacher_account Tables are as follows:
| ID | TEACHER_ID | LOGIN | PASWORD |
|---|---|---|---|
| 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
| ID | DESCRIPTION | FIRST_NAME | LAST_NAME | PHONE | |
|---|---|---|---|---|---|
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 |
| 4 | PYTHON | Enrique | harn | enriqueharn@yahoo.com | 452536425 |
| 5 | C++ | Kelvin | Mitnick | kelvinMitnick@yahoo.com | 453656854 |
| 6 | PYTHON | Michael | calce | Michaelcalce@yahoo.com | 487656854 |
| 7 | – | John | kart | Johncart@yahoo.com | 654256854 |
SQL JOINS
The SQL join is used to combine records from two or more tables in a DB. A Join is for combining fields from two tables by using values common to each.
Joining is used in SQL when more than one table is related in a Query. Thus, performing the Join operation helps you to get results from the related tables.
When Join two or more tables, a relationship is associated with several entities between two or more tables. The relationship is discussed below.
The Joins are done using ON keyword. After ON keyword, we can apply Condition. This condition is called the Join Condition.
When applying Primary and foreign keys, Understanding SQL JOINS is gonna be accurate. Because child table had values if it’s parent table has it.
During in JOINING, We can make more complex Queries by using ORDER BY, IN, WHERE, BETWEEN clauses. If you have not clear this concept then read this, https://shoutcoders.com/sql-preparation-time-note-3.
All Joining patterns are similar.
EQUI JOIN
Equi Join is simple SQL join.
Uses the equal sign (=) as the comparison operator for the condition.
Types of SQL Equi Join: INNER JOIN and OUTER JOIN.
NON-EQUI JOIN
Non Equi Join uses comparison operator other than the equal sign.
The operators uses like >, <, >=, <= with the condition.
Types of SQL Non Equi Join: CROSS JOIN
An SQL JOIN operation is based on a common field between them. There are 5 or 6 types of joining. In short, the Joining description is given below,
- INNER JOIN: Returns all rows when there is at least one match in BOTH tables.
- LEFT JOIN: Return all rows from the left table and the matched rows from the right table.
- RIGHT JOIN: Return all rows from the right table and the matched rows from the left table.
- FULL JOIN: Return all rows when there is a match in ONE of the tables.
- CARTESIAN JOIN: Return the Cartesian product of the sets of records from two or more joined tables.
- NATURAL JOIN: Return all rows when there is at least one match in BOTH tables.
- SELF JOIN: It can be viewed as a JOIN of two copies of the same table.
SQL INNER JOIN
The SQL Inner Join is also called Simple Join. This Join returns all rows from multiple tables as long as the join condition is met.
In the case of Joining two tables, It is easy. But, When we consider more than 2 Tables, the given below syntax is the only one joining syntax.
The common fields between these tables are anything like ID, ROLL_NO, EMP_ID, etc. It is easy to consider Primary and foreign keys or unique values.
Based on the condition, row selection is done and only those records are fetched in this INNER JOIN who satisfied the condition.
Records present in both tables are fetched. Overwrite like Cartesian Joining is not done.
The common fields after joining are shown twice or not, comment below.
Syntax:
|
1 2 3 4 5 |
SELECT * FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name INNER JOIN table3 ON table3.column_name=table1|2.column_name; Example: SELECT * FROM teacher INNER JOIN teacher_account ON teacher.id=teacher_account.teacher_id; |
| ID | DESCRIPTION | FIRST_NAME | LAST_NAME | PHONE | ID | TEACHER_ID | LOGIN | PASWORD | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
If you interested to depth of it, then read this article. https://www.sqltutorial.org/sql-inner-join/
SQL OUTER JOIN
As shown in the above diagram, There are LEFT JOIN, RIGHT JOIN AND FULL OUTER JOIN. Considering LEFT JOIN first and then coming to RIGHT JOIN AND FULL OUTER JOIN.
LEFT JOIN:
The LEFT JOIN returns all rows from the left table(table1) that does not depends on condition, and returns only the matching rows in the right table(table2). The result is NULL in the right side when there is no match.
Syntax:
|
1 2 3 4 |
SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name; Example: SELECT * FROM teacher LEFT JOIN teacher_account ON teacher.id=teacher_account.teacher_id; |
| ID | DESCRIPTION | FIRST_NAME | LAST_NAME | PHONE | ID | TEACHER_ID | LOGIN | PASWORD | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
| 6 | PYTHON | Michael | calce | Michaelcalce@yahoo.com | 487656854 | – | – | – | – |
| 7 | – | John | kart | Johncart@yahoo.com | 654256854 | – | – | – | – |
| 4 | PYTHON | Enrique | harn | enriqueharn@yahoo.com | 452536425 | – | – | – | – |
| 5 | C++ | Kelvin | Mitnick | kelvinMitnick@yahoo.com | 453656854 | – | – | – | – |
LEFT OUTER JOIN IS ANOTHER NAME OF LEFT JOIN.
RIGHT JOIN:
The RIGHT JOIN returns all rows from the right table(table2) that does not depends on condition, and returns only the matching rows in the left table(table1). The result is NULL in the left side when there is no match.
Syntax:
|
1 2 3 4 |
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name; Example: SELECT * FROM teacher RIGHT JOIN teacher_account ON teacher.id=teacher_account.teacher_id; |
| ID | DESCRIPTION | FIRST_NAME | LAST_NAME | PHONE | ID | TEACHER_ID | LOGIN | PASWORD | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
Here, Primary and Foreign key is considered. Thus, Accurate result is not get.
RIGHT OUTER JOIN IS ANOTHER NAME OF RIGHT JOIN.
FULL OUTER JOIN:
The FULL OUTER JOIN returns all rows from both the tables, it does not depends on condition. The result is NULL in the left side or right side when there is no match.
Syntax:
|
1 2 3 4 |
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name; Example: SELECT * FROM teacher FULL OUTER JOIN teacher_account ON teacher.id=teacher_account.teacher_id; |
| ID | DESCRIPTION | FIRST_NAME | LAST_NAME | PHONE | ID | TEACHER_ID | LOGIN | PASWORD | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 4 | PYTHON | Enrique | harn | enriqueharn@yahoo.com | 452536425 | – | – | – | – |
| 5 | C++ | Kelvin | Mitnick | kelvinMitnick@yahoo.com | 453656854 | – | – | – | – |
| 6 | PYTHON | Michael | calce | Michaelcalce@yahoo.com | 487656854 | – | – | – | – |
| 7 | – | John | kart | Johncart@yahoo.com | 654256854 | – | – | – | – |
Here, Primary and Foreign key is considered. Thus, Accurate result is not get.
CARTESIAN JOIN
The Cartesian product X×Y between two sets X and Y is the set of all possible ordered pairs with first element from X and second element from Y: X×Y={(x,y):x∈X and y∈Y}.
The Cartesian Join(Cross Join) is similar to Cartesian product when no condition is applied. This normally happens when no matching join columns are specified.
For example, if table A with 100 rows is joined with table B with 1000 rows, a Cartesian join will return 100,000 rows.
In this syntax, table_names are separated by comma(,). The Comma is used to implement Cartesian Join. The CROSS JOIN keyword is also used in some database systems.
Syntax:
|
1 2 3 4 5 |
SELECT * FROM table1 CROSS JOIN table2; SELECT * FROM table1, table2; SELECT * FROM table1 CROSS JOIN table2 where table1.column_name=table2.column_name; Example: SELECT * FROM teacher Cross JOIN teacher_account; |
| ID | DESCRIPTION | FIRST_NAME | LAST_NAME | PHONE | ID | TEACHER_ID | LOGIN | PASWORD | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 4 | PYTHON | Enrique | harn | enriqueharn@yahoo.com | 452536425 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 5 | C++ | Kelvin | Mitnick | kelvinMitnick@yahoo.com | 453656854 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 6 | PYTHON | Michael | calce | Michaelcalce@yahoo.com | 487656854 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 7 | – | John | kart | Johncart@yahoo.com | 654256854 | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 4 | PYTHON | Enrique | harn | enriqueharn@yahoo.com | 452536425 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 5 | C++ | Kelvin | Mitnick | kelvinMitnick@yahoo.com | 453656854 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 6 | PYTHON | Michael | calce | Michaelcalce@yahoo.com | 487656854 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 7 | – | John | kart | Johncart@yahoo.com | 654256854 | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
| 4 | PYTHON | Enrique | harn | enriqueharn@yahoo.com | 452536425 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
| 5 | C++ | Kelvin | Mitnick | kelvinMitnick@yahoo.com | 453656854 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
| 6 | PYTHON | Michael | calce | Michaelcalce@yahoo.com | 487656854 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
| 7 | – | John | kart | Johncart@yahoo.com | 654256854 | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
In CROSS JOIN, If any condition is there for matching two or more tables based on ID, or any common fields. Then, CROSS JOIN becomes NATURAL JOIN.
NATURAL JOIN
As mentioned earlier, EQUI JOIN performs join against equality or matching column(s) values of the associated tables, and an equal sign (=) is used as a comparison operator in the where clause to refer to equality.
The SQL NATURAL JOIN is a type of EQUI JOIN. So, It is not required to mention table1.column_name = table2.column_name;
The NATURAL JOIN is similar to CROSS JOIN with matching any common fields. The columns with the same name of associated tables will appear once only.
The associated tables have one or more pairs of identically named columns.
The columns must be the same data type.
Don’t use ON clause in a natural join.
Syntax:
|
1 2 3 |
SELECT * FROM table1 NATURAL JOIN table2; Example: SELECT * FROM teacher NATURAL JOIN teacher_account; |
| ID | DESCRIPTION | FIRST_NAME | LAST_NAME | PHONE | TEACHER_ID | LOGIN | PASWORD | |
|---|---|---|---|---|---|---|---|---|
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 | 2 | Tomriddle@yahoo.com | Tomriddle |
SELF JOIN
A SELF JOIN is a JOIN in which a table is joined with itself, specially when the table has a FOREIGN KEY which references its own PRIMARY KEY.
To join a table itself means that each row of the table is combined with itself and with every other row of the table.
The self join can be viewed as a join of two copies of the same table.
It is similar to CARTESIAN JOIN.
Syntax:
|
1 2 3 |
SELECT * FROM table_A X, table_A Y WHERE X.A=Y.A; Example: select * from teacher_account T , teacher_account Y where T.id=Y.id; |
| ID | TEACHER_ID | LOGIN | PASWORD | ID | TEACHER_ID | LOGIN | PASWORD |
|---|---|---|---|---|---|---|---|
| 1 | 1 | alfredskuster@yahoo.com | alfredskuster | 1 | 1 | alfredskuster@yahoo.com | alfredskuster |
| 2 | 3 | harryJonathan@yahoo.com | harryJonathan | 2 | 3 | harryJonathan@yahoo.com | harryJonathan |
| 3 | 2 | Tomriddle@yahoo.com | Tomriddle | 3 | 2 | Tomriddle@yahoo.com | Tomriddle |
RELATIONSHIP
A relationship is an association among several entities. A relationship specifies how two entities are related.
A relationship-type is a common part of a group of similar relationships.
A relationship-set is a set of relationships of the same type. The association between the entity-sets is referred to as participation.
A relationship instance represents an association between named entities in the real-world.
The function an entity plays in a relationship is called that entity’s role.
In general, the participating entity-sets in a relationship-set are distinct, thus roles are implicit and are not usually specified.
When an entity-set participates in a relationship-set more than once, in different roles, (the roles are explicitly specified for clarification.) is called a recursive relationship–set. (explicit role names are necessary to specify how an entity participates in a relationship instance.)
A relationship may also have attributes called descriptive attributes. Each relationship instance must be uniquely identifiable from its participating entities, without the descriptive attributes.
DEGREE OF RELATIONSHIP
Binary Relationship: If two entities are participating in a relationship.
Ternary Relationship: If three entities are participating in a relationship.
N-Array Relationship: If more than three entities are participating in a relationship.
These are called Degree of Relationship.
PARTICIPATION CONSTRAINTS
TOTAL PARTICIPATION
The participation of an entity-set E in a relationship R is said to be total if every entity participates at least once in R.
If all entities from an entity set participate in the relation it is called Total participation and it is described by DOUBLE Line.
PARTIAL PARTICIPATION
If only some entities in E participate in relationships in R the participation of E in R is said to be partial.
If NOT all entities from an entity set participate in the relation it is called Total participation and it is described by SINGLE Line.
MAPPING CARDINALITY OR CARDINALITY RATIO
Mapping cardinality express the number of entities to which another entity can be associated via a relationship-set.
Mapping cardinalities most useful in describing binary relationship-sets.
For a binary relationship R between entity-set A and B, the mapping cardinality must be one of the following types, One-to-one, One-to-many, Many-to-one, or Many-to-many.
FAQ:
Difference between Natural Join and Inner Join?
There is one significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned. In INNER JOIN, The columns with the same name of associated tables will appear twice. In NATURAL JOIN, The columns with the same name of associated tables will appear once only.
Difference between Cartesian Join and Self Join?
There is one significant difference between CARTESIAN JOIN and SELF JOIN is the number of tables related in a Relation. In CARTESIAN JOIN, More than one table is associated in a relation whereas, In SELF JOIN, it can be viewed as a join of two copies of the same table.
What is an Entity?
An entity is a thing or object in the real world that is distinguishable from all other objects. For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. All these entities have some attributes or properties that give them their identity.
In a database an entity is a table. The table represents whatever real world concept you are trying to model (person, transaction, event). Constraints can represents relationships between entities.
What is Entity Set?
An entity set is a set of entities of the same type that share the properties or attributes. The individual entities that constitute a set are said to be an extension of an entity set. Entity set need not be disjoint. For example, the entity set employee (all employees of a bank) and the entity set customer (all customers of the bank) may have members in common.
What is Attribute?
An entity is represented by a set of attributes that have descriptive properties processed by each number of an entity set. For each attribute, there is a set of permitted values called the domain, or value set of the attribute.
Formally, an attribute is a function that maps from the entity into a domain.
Attributes can be Simple, Composite, Single, Multi value attribute or Null value attribute.
What is Simple Attribute?
The attribute which cannot be derived into sub parts is called Simple Attribute.
What is Composite Attribute?
Attribute those can be divided into sub parts is called composite attribute.
What is Single Value attribute?
The attribute which has a single value for a particular entity is called a single value attribute. Example: Pan card, Roll_No, etc.
What is Multi Value attribute?
The attribute which has multiple values for a particular entity is called the multi-value attribute.
What is Derived attribute?
The value of these types of attributes can be derived from the value of other related attributes or entities. Example: Age from DOB.
What is Null attribute?
An attribute takes null when an entity doesn’t have a value for it. May indicate “not applicable” or “unknown” (“missing”, “not known”). Example: Address 2. Many families only have one permanent address, thus leave it as Null.
Difference between Simple and Single value attribute?
In Simple attribute, attribute cannot be divided in to subpart(No subpart). For example, First_name. Whereas, In Single Value attribute, attribute cannot contain multiple values(No double value). For example, DOB.
The Single value attribute can be derived as a value of other related attributes. For example, Age from DOB.
What is Instance and Schema?
Database change overtime as information is inserted and deleted. The collection of information in the database at a particular moment is called an instance.
The overall design/ description of the database is called Schema.
What is SQL?
SQL stands for structure Query Language, Is a special purpose programming language design for managing data to a relational management system. It can define the structure of data, modify data in the database and specify security constant i.e. it contain DDL, DML, and DCL statements.
What is One-to-One Mapping?
An entity in A is associated with atmost one entity in B, and An entity in B is associated with atmost one entity in A. Example: One loan can be borrowed by only one customer. Similarly, One customer can be associated with only one loan.
What is One-to-Many Mapping?
An entity in A is associated with any number (0 or more) of entities in B, and An entity in B is associated with atmost one entity in A. Example: A customer can have many loans. But, one loan_number can be associated with only one customer.
What is Many-to-One Mapping?
An entity in A is associated with atmost one entity in B, And An entity in B, however, can be associated with any number(0 or more) of entities in A. Example: A student can belong to one Semester. However, A semester can contain many students.
What is Many-to-Many Mapping?
An entity in A is associated with any number (0 or more) of entities in B, and An entity in B can be associated with any number(0 or more) of entities in A. Example: A student can taught by many teachers as well as, A teacher can teach many students.
You might like:
The most common portions of SQL is already covered in SQL PREPARATION TIME NOTE 1, SQL PREPARATION TIME NOTE 2, and SQL PREPARATION TIME NOTE 3. In those articles, You can get your confidence over SQL commands.
