What is Self Join in SQL ?

 

 In the previous section, we have discussed what is joining and its types, in this section we will discuss the rest of the types.

Self-join in SQL:

Sometime we need to retrieve rows of the same table by applying some constraints to its fields. In these types of cases, self-join is the best option. It is the type of join in which one table is joined with itself depending on some condition that is specified in WHERE CLAUSE, means that the rows of the same table are compared for generating results. So self-join is used to merge the rows of the same table by matching them. There might be an error while writing the Query of self-join if the user will use the same table name twice after FROM keyword, so for resolving that problem we can use the different Alias of the same table.

Syntax of Self Join:

SELECT column_name(N)
FROM table1 T 1 , table1 T 2
WHERE condition;

Here T1 and T2 are the aliases of table1. So suppose we have Students table.

Self Join Mysql Self Join Oracle

Example of Self-join:

 For example, if there is a student table and it has the column CR (class representative) so we can apply the self-join to extract the data of students along with their CR. In this case, we suppose that Std_Id has the primary key and CR is the FOREIGN KEY that takes REFERENCE  from Std_Id PK. So Foreign keys can have duplicate values.  So we can apply the self join in the table in which according to one specific column (in this case Std_Id) we can have duplicate values in another column (in this case CR column) and we can join the table on the basis of these two columns. So as we know the class representative can represent more than one student of the class so we can apply the self-join query on Students table.



Self Join In Sql Example Student Proctor
In this query S and C are the aliases and joining is done on the basis of Std_Id and CR columns of the same table.

Result:


Sql Self Join Subquery

Proctor name is a new column that is generated as the result of self-join. hope so self-join is concept is cleared. keep learning.

Comments

Post a Comment

your feedback is valuable.

Popular posts from this blog

SQL statements (Data Manipulation Language)

Image
  Writing SQL Commands: Before knowing what are DML commands have a glance at what are SQL commands and how to write these commands. well, Commands are the SQL statements that are written by following some syntax to extract data from the database mainly and later on perform some other operations to manage or retain the data according to constraint(rules) in the database. SQL statements consist of reserved words and User-defined words. Reserved words have some specific meanings and can be as it is. They must be spelled properly as required and can not be split across lines such as SELECT ,ALTER,UPDATE etc. User-defined words are created by the user (according to the syntax rule) and represent the name of various database objects such as table column Views etc. Although SQL is a free format language the statements can be made more readable by following these steps. Ø Each clause in a statement should begin on a new line. Ø Uppercase letters are used to represent Reserved ...
Read more

What is SQL

Image
  What Is SQL? SQL is a structured query language. It was developed by IBM (international business Machine). It is endorsed as a national standard by the American national standard institute. It is the language that is used frequently for relational data models. SQL works with database programs like MS Access, DB2, Informix, Oracle, MS SQL Server, Sybase. What is SQL used for: SQL is used for extracting data from the database. Now,  what is the database?  A database is a large collection of data that is organized in a good manner way so that the user can access data efficiently and that data is stored in a compact(taking less space) manner on a computer. when we talk about Database here is another term used parallelly with it which is DBMS . Database Management System (DBMS) is a program that is used to create and maintain the data of the database. Let’s suppose we have a database “Employee” that contain the information of all employees who work in an organization ...
Read more

Types of joins in SQL Equi and Non_Equi join

Image
  Types of Joins in SQL: As the starter of SQL when you will apply any query it has some limitation that the resultant table is generated from one table only but there is some situations arise in which we may have to retrieve data from multiple tables and in that case we will use JOIN operation. If we want to generate the joined table (which will be the resultant table) from row pairing then two tables must have columns with the same values. Joining is the process of combining rows from multiple tables. Through joining, we can generate a short-lived table with all accessed columns from multiple tables that are specified in join. So here we will discuss different types of joins in SQL. How can we perform SQL joins: For performing JOIN we can use more than one table name after FROM keyword. These are obviously the name of those tables from which we want to extract data. Both table names should be written by using a separator or comma between them. WHERE Clause is used to specify ...
Read more