SQL - Self join

Data System Architecture

About

A join does not always have to involve two different tables. You can join a table to itself, creating a self-join. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.

You can:

  • answer practical question such as which products in the Product table have the same names but different types of packaging?
  • or build a hierarchy

Example

table2
(inner and outer set)
Column ID Column ID_2
A
B A
C C
gerardnico@orcl>select t1.id "ID_table2"
  2        from table2 t1, table2 t2
  3       where  t1.id = t2.id_2;

ID_table2
----------
A
C





Discover More
Snowflakeschema
Dimensional Data Modeling - Snowflake schema

A snowflake schema is a star schema with fully normalised (3NF) dimensions. It gets its name from that it has a similar shape than a snowflake. A snowflake is a dimensional model : in which a central...
Joiner Operator
OWB - Joiner operator

The Joiner operator joins multiple row sets from different sources with different cardinalities, and produces a single output row set. The Joiner operator results in a WHERE clause in the generated SQL...
Data System Architecture
SQL - Joins (Home)

in SQL. A join is a SQL clause statement which define the set operation such as: intersection, union, that the database must perform between two or more relation (table, view, query, ...). The...
Data System Architecture
What are the LAG and LEAD SQL functions ?

lag and lead are Sql window functions that provide access to a row at a given offset prior to/after the current row position.



Share this page:
Follow us:
Task Runner