SQL PREPARATION 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.

Table Structure
Table Structure

Teacher and Teacher_account Tables are as follows:

IDTEACHER_IDLOGINPASWORD
11alfredskuster@yahoo.comalfredskuster
23harryJonathan@yahoo.comharryJonathan
32Tomriddle@yahoo.comTomriddle
Teacher_account Table
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
1DBMSAlfredskusteralfredskuster@yahoo.com572222132
2C++TomRiddleTomriddle@yahoo.com454522132
3DBMSHarryJonathanharryJonathan@yahoo.com258656854
4PYTHONEnriqueharnenriqueharn@yahoo.com452536425
5C++KelvinMitnickkelvinMitnick@yahoo.com453656854
6PYTHONMichaelcalceMichaelcalce@yahoo.com487656854
7JohnkartJohncart@yahoo.com654256854
Teacher Table

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 JOINS
SQL JOINS

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:
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONEIDTEACHER_IDLOGINPASWORD
1DBMSAlfredskusteralfredskuster@yahoo.com57222213211alfredskuster@yahoo.comalfredskuster
3DBMSHarryJonathanharryJonathan@yahoo.com25865685423harryJonathan@yahoo.comharryJonathan
2C++TomRiddleTomriddle@yahoo.com45452213232Tomriddle@yahoo.comTomriddle
INNER JOIN

If you interested to depth of it, then read this article. https://www.sqltutorial.org/sql-inner-join/

INNER AND OUTER JOIN
INNER AND OUTER 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:

IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONEIDTEACHER_IDLOGINPASWORD
1DBMSAlfredskusteralfredskuster@yahoo.com57222213211alfredskuster@yahoo.comalfredskuster
3DBMSHarryJonathanharryJonathan@yahoo.com25865685423harryJonathan@yahoo.comharryJonathan
2C++TomRiddleTomriddle@yahoo.com45452213232Tomriddle@yahoo.comTomriddle
6PYTHONMichaelcalceMichaelcalce@yahoo.com487656854
7JohnkartJohncart@yahoo.com654256854
4PYTHONEnriqueharnenriqueharn@yahoo.com452536425
5C++KelvinMitnickkelvinMitnick@yahoo.com453656854
LEFT JOIN
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:

IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONEIDTEACHER_IDLOGINPASWORD
1DBMSAlfredskusteralfredskuster@yahoo.com57222213211alfredskuster@yahoo.comalfredskuster
3DBMSHarryJonathanharryJonathan@yahoo.com25865685423harryJonathan@yahoo.comharryJonathan
2C++TomRiddleTomriddle@yahoo.com45452213232Tomriddle@yahoo.comTomriddle
Right Join

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:

IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONEIDTEACHER_IDLOGINPASWORD
1DBMSAlfredskusteralfredskuster@yahoo.com57222213211alfredskuster@yahoo.comalfredskuster
2C++TomRiddleTomriddle@yahoo.com45452213232Tomriddle@yahoo.comTomriddle
3DBMSHarryJonathanharryJonathan@yahoo.com25865685423harryJonathan@yahoo.comharryJonathan
4PYTHONEnriqueharnenriqueharn@yahoo.com452536425
5C++KelvinMitnickkelvinMitnick@yahoo.com453656854
6PYTHONMichaelcalceMichaelcalce@yahoo.com487656854
7JohnkartJohncart@yahoo.com654256854
Full Outer Join

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:
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONEIDTEACHER_IDLOGINPASWORD
1DBMSAlfredskusteralfredskuster@yahoo.com57222213211alfredskuster@yahoo.comalfredskuster
2C++TomRiddleTomriddle@yahoo.com45452213211alfredskuster@yahoo.comalfredskuster
3DBMSHarryJonathanharryJonathan@yahoo.com25865685411alfredskuster@yahoo.comalfredskuster
4PYTHONEnriqueharnenriqueharn@yahoo.com45253642511alfredskuster@yahoo.comalfredskuster
5C++KelvinMitnickkelvinMitnick@yahoo.com45365685411alfredskuster@yahoo.comalfredskuster
6PYTHONMichaelcalceMichaelcalce@yahoo.com48765685411alfredskuster@yahoo.comalfredskuster
7JohnkartJohncart@yahoo.com65425685411alfredskuster@yahoo.comalfredskuster
1DBMSAlfredskusteralfredskuster@yahoo.com57222213223harryJonathan@yahoo.comharryJonathan
2C++TomRiddleTomriddle@yahoo.com45452213223harryJonathan@yahoo.comharryJonathan
3DBMSHarryJonathanharryJonathan@yahoo.com25865685423harryJonathan@yahoo.comharryJonathan
4PYTHONEnriqueharnenriqueharn@yahoo.com45253642523harryJonathan@yahoo.comharryJonathan
5C++KelvinMitnickkelvinMitnick@yahoo.com45365685423harryJonathan@yahoo.comharryJonathan
6PYTHONMichaelcalceMichaelcalce@yahoo.com48765685423harryJonathan@yahoo.comharryJonathan
7JohnkartJohncart@yahoo.com65425685423harryJonathan@yahoo.comharryJonathan
1DBMSAlfredskusteralfredskuster@yahoo.com57222213232Tomriddle@yahoo.comTomriddle
2C++TomRiddleTomriddle@yahoo.com45452213232Tomriddle@yahoo.comTomriddle
3DBMSHarryJonathanharryJonathan@yahoo.com25865685432Tomriddle@yahoo.comTomriddle
4PYTHONEnriqueharnenriqueharn@yahoo.com45253642532Tomriddle@yahoo.comTomriddle
5C++KelvinMitnickkelvinMitnick@yahoo.com45365685432Tomriddle@yahoo.comTomriddle
6PYTHONMichaelcalceMichaelcalce@yahoo.com48765685432Tomriddle@yahoo.comTomriddle
7JohnkartJohncart@yahoo.com65425685432Tomriddle@yahoo.comTomriddle
CROSS JOIN

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:
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONETEACHER_IDLOGINPASWORD
1DBMSAlfredskusteralfredskuster@yahoo.com5722221321alfredskuster@yahoo.comalfredskuster
2C++TomRiddleTomriddle@yahoo.com4545221323harryJonathan@yahoo.comharryJonathan
3DBMSHarryJonathanharryJonathan@yahoo.com2586568542Tomriddle@yahoo.comTomriddle
NATURAL JOIN

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:

IDTEACHER_IDLOGINPASWORDIDTEACHER_IDLOGINPASWORD
11alfredskuster@yahoo.comalfredskuster11alfredskuster@yahoo.comalfredskuster
23harryJonathan@yahoo.comharryJonathan23harryJonathan@yahoo.comharryJonathan
32Tomriddle@yahoo.comTomriddle32Tomriddle@yahoo.comTomriddle
SELF JOIN
ER MODEL
ER MODEL

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 relationshipset. (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.

Total and Partial Participation
Total and Partial Participation

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.

Mapping Cardinality
Mapping Cardinality Type 1
Mapping Cardinality Type 2

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:

C program to reverse a Number

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Close Bitnami banner
Bitnami