Contents
The contents of this work are based on;
This text on SQL is based on the EP-European Parliament as Data Model.
The European Parliament (EP) is the parliamentary institution of the European Union (EU).
The European Parliament is elected by the citizens of the European Union to represent their interests.
Its origins go back to the 1950s and the founding Treaties. Since 1979 its members have been directly elected by the citizens of the EU.
Elections are held every five years, and every EU citizen is entitled to vote, and to stand as a candidate, wherever they live in the EU. Parliament thus expresses the democratic will of the European Union's nearly 500 million citizens and it represents their interests in discussions with the other EU institutions.
Please Note:
The sources for the data, which are all freely obtainable from the European Union website, and from several leaflets of the EU institutions, are listed in the References section.
These sources are used to create the fictitious Business Narrative of the "Data Modelling and Normalization" Chapter.
We called this Business Narrative; "the Story of the European Union" which explains about the EU and EP, for purposes of Data Modeling.
The Story of the European Union is the last Chapter of Kaye is Learning SQL.
This text on SQL is based on the Oracle Database as RDBMS — Relational Database Management System.
Although the SQL Statements used in this text, apply specifically to the Oracle Database implementation of SQL, they are generally compatible with many other RDBMS SQL implementations.
The knowledge in the following sections are based on my personal work experience, as well as on several Oracle Documentation, which are freely downloadable from Oracle websites.
You can find a list of these documents in the References.
The name of the documents may change in time.
You can refer to the Oracle Documentation if you need more detailed information on any of the topics in this text.
Any errors present, are mine.
So far we have selected data from only one table.
However, sometimes we might need to access information that has to be retrieved from more than one table.
For example, we might need to display Members of the Parliament who belong to a certain Political Group. We would like to see some details of the Political Group along with some details of the Members of the Parliament in our report.
Below, we see the structure of the MEPS table which holds details of the Members of the Parliament, and the POLITICAL_GROUPS table which holds details of the Political Groups.
MEPS Table
POLITICAL_GROUPS Table
As we already know, these two tables have a Foreign Key Relationship.
Below is the Creation Script for Table MEPS, and the Foreign Key Relationship between MEPS and POLITICAL_GROUPS is:
,pg_id number(10)
constraint pg_id_fk
references POLITICAL_GROUPS(pg_id)
Creation Script for Table MEPS
create table MEPS
(mep_id number(10)
constraint mep_id_pk primary key
,first_name varchar2(120)
,last_name varchar2(120)
constraint last_name_nn not null
,salary number(9,2)
,bonus_pct number(5,4)
,email varchar2(240)
constraint email_nn not null
constraint email_unique unique
,tel varchar2(20)
,internet varchar2(120)
,start_date date
,country_id varchar2(4)
constraint country_id_fk
references COUNTRIES(country_id)
,pg_id number(10)
constraint pg_id_fk
references POLITICAL_GROUPS(pg_id)
,pg_chair_or_cochair1 number(10)
constraint pg_chair_cochair1_fk
references MEPS(mep_id)
,pg_cochair2 number(10)
constraint pg_cochair2_fk
references MEPS(mep_id)
);
Here is the Constraints Tab of MEPS where we can see the Constraints created by the script above.
The FOREIGN KEY Relationship (FOREIGN KEY Constraint) PG_ID_FK requires that;
We use Foreign Key Relationship to JOIN Tables and retrieve the Rows of our Choice.
Display LAST NAME and FIRST NAME of the Members of the Parliaments, as well as their POLITICAL GROUP CODE and POLITICAL GROUP NAME.
select m.last_name , m.first_name , p.pg_code , p.pg_name from meps m JOIN political_groups p ON (m.pg_id=p.pg_id) order by p.pg_code , m.last_name , m.first_name;
The Result Set is displayed below.
Not all rows are displayed.
from meps m JOIN political_groups p
To join the two tables, we specified their names in the FROM Clause.
We used the JOIN Keyword.
We used the alias m for table MEPS.
We used the alias p for table POLITICAL_GROUPS.
ON (m.pg_id=p.pg_id)
To join the two tables, we specified a JOIN Condition in the ON Clause.
The columns used in the ON Clause are the Foreign Key Relationship columns of table MEPS and table POLITICAL_GROUPS.
These Foreign Key Relationship columns both have the same name in the two tables: pg_id
To eliminate ambiguity;
To join two tables together, we specified a single JOIN Condition / only one JOIN Condition in the ON Clause.
If we were joining three tables together, we would have specified at least two JOIN Conditions in the ON Clause.
To JOIN n tables together, we need a minimum of n-1 JOIN Conditions.
select m.last_name , m.first_name , p.pg_code , p.pg_name
An INNER JOIN which is sometimes called a SIMPLE JOIN is a join of two or more tables that RETURNS ONLY those rows that satisfy the JOIN Condition.
The previous example, where the SELECT Statement can also be seen below, is an examle of INNER JOIN / SIMPLE JOIN .
select m.last_name
, m.first_name
, p.pg_code
, p.pg_name
from meps m
JOIN political_groups p
ON (m.pg_id=p.pg_id)
order by p.pg_code
, m.last_name
, m.first_name;
The Sample SELECT Statement above returns Matching Rows of tables MEPS and POLITICAL_GROUPS based on the JOIN Condition:
FROM MEPS M
JOIN POLITICAL_GROUPS P
ON (M.PG_ID = P.PG_ID)
ONLY "Members of the Parliament that BELONG TO a Political Group" are RETURNED.
ONLY "Political Groups that HAVE Members of the Parliament" are RETURNED.
The Sample SELECT Statement above returns Matching Rows of tables MEPS and POLITICAL_GROUPS based on the JOIN Condition:
FROM MEPS M
JOIN POLITICAL_GROUPS P
ON (M.PG_ID = P.PG_ID)
If a Member of the Parliament does NOT belong to a Political Group, in which case their PG_ID is NULL, then they will NOT be returned by this query.
If there is a NEW Political Group in our Database, which have NO Members yet, this Political Group will NOT be returned by the query above.
The query below lists the Members of the Parliaments who do NOT belong to any Political Groups.
Members who do not belong to any of the groups are known as "non-attached Members — non-inscrits (NI)".
In our database, we DO NOT ASSIGN a PG_ID to them.
They have a NULL value in their PG_ID column.
We select the "non-attached Members — non-inscrits (NI)" in our database using the following SELECT Statement:
SELECT MEP_ID, LAST_NAME, FIRST_NAME, COUNTRY_ID, PG_ID FROM MEPS WHERE PG_ID IS NULL ORDER BY MEP_ID
Because their PG_ID is NULL, the "non-attached Members" who do not belong to a Political Group will NOT be returned by the INNER JOIN Query of the previous section.
This was the INNER JOIN Query:
select m.last_name
, m.first_name
, p.pg_code
, p.pg_name
from meps m
JOIN political_groups p
ON (m.pg_id=p.pg_id)
order by p.pg_code
, m.last_name
, m.first_name;
Remember that a NULL is NOT Equal to any other value!
A NULL is NOT Equal to any other NULL either!!
Therefore, the INNER JOIN Condition above CANNOT be satisfied.
Hence, the "non-attached Members — non-inscrits (NI)" in our database, will NOT be returned by this SELECT Statement.
There is definitely a need for another type of JOIN.
If we want to display the details of the "non-attached Members — non-inscrits (NI)" along with the other Members of the Parliament in the results of the query above, we can then use another type of JOIN called the OUTER JOIN.
The query below lists all the Political Groups in our database, whether they have any members or not.
In fact, we have two newly formed Political Groups, that HAVE NO MEMBERS YET:
SELECT * FROM POLITICAL_GROUPS ORDER BY PG_ID
The two new Political Groups which DO NOT HAVE MEMBERS YET, are displayed with the SELECT Statement above.
What if we want to include the details of these new Political Groups in the results of our INNER JOIN Query seen below?
Can we see these two new Political Groups?
select m.last_name
, m.first_name
, p.pg_code
, p.pg_name
from meps m
JOIN political_groups p
ON (m.pg_id=p.pg_id)
order by p.pg_code
, m.last_name
, m.first_name;
It is NOT Possible to see the two new Political Groups with our INNER JOIN Condition.
This is because;
Hence, we need another type of JOIN.
This new type of JOIN which will display the new Political Groups with NO MEMBERS YET, is called the OUTER JOIN.
An OUTER JOIN extends the result of a INNER / SIMPLE JOIN.
An OUTER JOIN returns all rows that satisfy the join condition, and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
The SELECT Statement below, selects details of all MEPS in the database, even though they DO NOT belong to a Political Group.
As we have seen previously, these Members are called "non-attached Members — non-inscrits (NI)", and their PG_ID column has a NULL Value.
The SELECT Statement below, is an example of a LEFT OUTER JOIN.
For all rows in table MEPS that have no matching rows in table POLITICAL_GROUPS, database returns NULL for any select list expressions containing columns of table POLITICAL_GROUPS.
For the "non-attached Members";
select m.last_name,
m.first_name,
nvl(p.pg_code,'NI') "PG Code",
nvl(p.pg_name,'Non-Attached Member') "PG Name"
from meps m LEFT OUTER JOIN
political_groups p
ON (m.pg_id=p.pg_id)
order by pg_code desc,
m.last_name,
m.first_name;
In this query;
Here is a definition of LEFT OUTER JOIN:
***
FROM TABLE_A LEFT OUTER JOIN TABLE_B
For all rows in TABLE_A that have NO matching rows in TABLE_B, database returns NULL for any select list expressions containing columns of TABLE_B.
***
I also like the following definition for LEFT OUTER JOIN:
***
FROM TABLE_ON_THE_LEFT LEFT OUTER JOIN TABLE_ON_THE_RIGHT
In a LEFT OUTER JOIN, the TABLE_ON_THE_LEFT in the FROM Clause is OUTER JOINED to the TABLE_ON_THE_RIGHT in the FROM Clause.
This is what a LEFT OUTER JOIN is.
Table on the LEFT is OUTER JOINED to the Table on the RIGHT.
***
Hence, as in the first definition above;
For all rows in TABLE_ON_THE_LEFT that have NO matching rows in TABLE_ON_THE_RIGHT, database returns NULL for any select list expressions containing columns of TABLE_ON_THE_RIGHT.
The SELECT Statement below, selects details of all the Countries in the database, whether they have any Members in the European Parliament or not.
This SELECT Statement, is an example of a RIGHT OUTER JOIN.
For all rows in table COUNTRIES that have no matching rows in table MEPS, database returns NULL for any select list expressions containing columns of table MEPS.
Hence, if there are no matching rows in table MEPS which is the Table on the LEFT or the LEFT-HAND-SIDE Table in the FROM Clause, the RIGHT OUTER JOIN will return NULLS for the following columns:
select c.country_id,
c.country_name,
m.last_name,
m.first_name,
m.pg_id
from meps m
RIGHT OUTER JOIN countries c
ON (m.country_id=c.country_id)
order by c.country_id,
m.last_name,
m.first_name;
In this query;
Here is a definition of RIGHT OUTER JOIN:
FROM TABLE_A RIGHT OUTER JOIN TABLE_B
For all rows in TABLE_B that have NO matching rows in TABLE_A, database returns NULL for any select list expressions containing columns of TABLE_A .
I also like the following definition for RIGHT OUTER JOIN:
***
FROM TABLE_ON_THE_LEFT RIGHT OUTER JOIN TABLE_ON_THE_RIGHT
In a RIGHT OUTER JOIN, the TABLE_ON_THE_RIGHT in the FROM Clause is OUTER JOINED to the TABLE_ON_THE_LEFT in the FROM Clause.
This is what a RIGHT OUTER JOIN is.
Table on the RIGHT is OUTER JOINED to the Table on the LEFT.
***
Hence, as in the first definition above;
For all rows in TABLE_ON_THE_RIGHT that have NO matching rows in TABLE_ON_THE_LEFT, database returns NULL for any select list expressions containing columns of TABLE_ON_THE_LEFT.
We want to write a query which displays all of the following:
The following SELECT Statement which uses a FULL OUTER JOIN does just that.
select m.last_name,
m.first_name,
p.pg_id "Political Group ID",
nvl(p.pg_code,'NI') "PG Code",
nvl(p.pg_name,'Non-Attached Member') "PG Name"
from meps m
FULL OUTER JOIN
political_groups p
ON (m.pg_id=p.pg_id)
order by p.pg_id,
m.last_name,
m.first_name;
Here is a definition of FULL OUTER JOIN:
***
FROM TABLE_ON_THE_LEFT FULL OUTER JOIN TABLE_ON_THE_RIGHT
***
To write a query that returns;
and
and
Below we can see the contents of the COMMITTEES table.
This table holds details of the Committees of the EP.
COMM_ID column is the Primary Key of this table.
select comm_id,
comm_code,
comm_type,
comm_name
from committees
order by comm_id;
We also have a table named MEP_COMM_DELG_ROLES, which holds information on;
Since the Members of the EP are very active and one MEP takes part in more than one Committee and Delegation, this table is loaded with information.
Here is the structure of this table:
Here is the ER Diagram of the European Parliament.
We came up with this ER Diagram in Chapter 2 (Data Modelling and Normalization), which is in 3rd Normal Form, i.e. 3NF.
Below is the CREATE TABLE Command for the table MEP_COMM_DELG_ROLES.
create table MEP_COMM_DELG_ROLES
(mep_id number(10)
constraint mep_comm_delg_roles_mep_id_fk
references MEPS(mep_id)
constraint mep_comm_delg_roles_mep_id_nn not null
,role_id number(10)
constraint mep_comm_delg_roles_role_id_fk
references ROLES(role_id)
constraint mep_comm_delg_roles_role_id_nn not null
,comm_id number(10)
constraint mep_comm_delg_roles_comm_id_fk
references COMMITTEES(comm_id)
,delg_id number(10)
constraint mep_comm_delg_roles_delg_id_fk
references DELEGATIONS(delg_id)
, constraint mep_comm_delg_roles_un
unique (mep_id, role_id, comm_id, delg_id)
);
comment on table MEP_COMM_DELG_ROLES is
'This table holds roles of MEPs in the EP Committees and EP Delegations only.
The values in the ROLE_ID Column of the table can be one of the following:
110=Chairman/Chairwoman of a Committee or a Delegation,
140=Vice Chairman/Vice Chairwoman of a Committee or a Delegation,
170=Member in a Committee or a Delegation,
180=Substitute in a Committee or in a Delegation
';
We can see below the Constraints of the table created by the CREATE TABLE Command.
This table, table MEP_COMM_DELG_ROLES only holds the Primary Key Values of the following tables:
Therefore, each of the columns MEP_ID, ROLE_ID, COMM_ID, and DELG_ID of the table MEP_COMM_DELG_ROLES are Foreign Keys Referencing the Primary Keys of MEPS, ROLES, COMMITTEES, and DELEGATIONS tables respectively.
We are asked to create a report for the Conference of the Presidents which displays;
In order to retrieve the information requested from us, we need to access these tables:
Below we can see the query of this report and some of its output.
select cm.comm_code "Code",
cm.comm_name "Name",
m.last_name||' '||m.first_name "Member of the EP"
from mep_comm_delg_roles mr
JOIN meps m
ON (mr.mep_id =m.mep_id)
JOIN committees cm
ON (mr.comm_id=cm.comm_id)
order by cm.comm_code,
m.last_name;
We can see the JOIN Clause of the report below:
from mep_comm_delg_roles mr
JOIN meps m
ON (mr.mep_id =m.mep_id)
JOIN committees cm
ON (mr.comm_id=cm.comm_id)
To JOIN THREE TABLES, we USED TWO JOINS.
This is the MINIMUM NUMBER of JOINS NECESSARY to JOIN THREE TABLES.
TO JOIN N TABLES, WE NEED A MINIMUM OF N-1 JOINS.
A SELF JOIN is a type of join, where a table is joined onto itself.
We need to use this type of join, when all the information we need is in a Single Table, but in Different Rows of that table.
Let's say we want to create a report which displays;
The report will look like the following — not all rows are displayed.
In the report output displayed above, we can see that the Members of the Parliament Vito Bonsignore (in the first row), Gunnar Hökmark, and many others are in the Political Group which is headed by Joseph Daul, i.e. Joseph Daul is the Chair of these MEPS.
Likewise, Members of the Parliament Bernard Wojciechowski (in the last row), Witold Tomczak, and many others are in the Political Group which is headed by Hanne Dahl, i.e. Hanne Dahl is the Chair of these MEPS.
All the information above come from table MEPS.
Structure of table MEPS is as follows:
We query these columns using the following SELECT Statement.
select mep_id,
first_name,
last_name,
pg_chair_or_cochair1
from meps
order by pg_chair_or_cochair1, mep_id;
Below are some of the output images of the SELECT Statement above. Not all rows are displayed.
The first row returned from the query has:
But we do not see the FIRST_NAME and LAST_NAME of the Chair in this row.
***
The second row returned from the query has:
But we do not see the FIRST_NAME and the LAST_NAME of the Chair in this row, either.
However, looking at the query output, we can see that the Chair of the Members in the first row and the second row is the same person; the person with PG_CHAIR_OR_COCHAIR1 = 20001.
PG_CHAIR_OR_COCHAIR1 = 20001 means that MEP_ID of the Chair = 20001.
How can we find the FIRST_NAME and the LAST_NAME of this person (MEP_ID = 20001) and print these details in the same row with the members Vito Bonsignore and Gunnar Hökmark and many others, as seen in the Report Output above?
This Chair Person is also a Member of the Parliament.
Since this person is also a Member of the Parliament, his details are in the MEPS table as details of all the other Members are.
This Chair Person has MEP_ID = 20001.
We find the row with MEP_ID=20001.
This row has the values; FIRST_NAME = 'Joseph', LAST_NAME = 'Daul', and PG_CHAIR_OR_COCHAIR1 = (null).
We found the FIRST_NAME and the LAST_NAME of the Chair (MEP_ID = 20001) . We can now print these details in the same row with the members as requested in the Report.
(null) is the display value for NULL in SQL Developer preferences. Hence we know that PG_CHAIR_OR_COCHAIR1 IS NULL.
A NULL value in the PG_CHAIR_OR_COCHAIR1 column indicates that this person (Joseph Daul) does not have a Chair — which is correct!
We can actually keep going through the algorithm in the paragraphs above, and create the Report Output above manually.
Manually ? 🤭 🤣 🤗
It would take us some time ...
🌱 🌿 🌳
But still, we can create the report requested from us.
However, there is a much more simple and definitely a quicker way than creating this report manually.
It is called a SELF-JOIN.
In a SELF-JOIN, we join a table onto itself.
In an ordinary join, we usually join the Foreign Key Column of one table, to the Primary Key Column of another table — the tables we want to retrieve information from.
In a SELF-JOIN, we join the Foreign Key Column of a table, to the Primary Key Column of the same table — because this table is the only table we can get the required information from.
In fact, there actually is a Foreign Key - Primary Key Relationship within table MEPS.
Such a table is called a Self-Referencing Table.
We can see below part of our ER Diagram with table MEPS, and the Self-Referencing Relationship.
Below is the CREATE TABLE Statement of table MEPS.
We can see two self-referencing relationships in this DDL (Data Definition Language) Command:
create table MEPS
(mep_id number(10)
constraint mep_id_pk primary key
,first_name varchar2(120)
,last_name varchar2(120)
constraint last_name_nn not null
,salary number(9,2)
,bonus_pct number(5,4)
,email varchar2(240)
constraint email_nn not null
constraint email_unique unique
,tel varchar2(20)
,internet varchar2(120)
,start_date date
,country_id varchar2(4)
constraint country_id_fk
references COUNTRIES(country_id)
,pg_id number(10)
constraint pg_id_fk
references POLITICAL_GROUPS(pg_id)
,pg_chair_or_cochair1 number(10)
constraint pg_chair_cochair1_fk
references MEPS(mep_id)
,pg_cochair2 number(10)
constraint pg_cochair2_fk
references MEPS(mep_id)
);
We now build up the SELECT Statement for the SELF-JOIN Report.
We go through the following steps to build up the SELECT Statement.
The Join Condition is written like above, because;
Our SELECT statement which has a Self Join will be:
select mep.first_name||' '||mep.last_name "MEP Name",
chair.first_name||' '||chair.last_name "Chair Name"
from meps mep
join meps chair
on (mep.pg_chair_or_cochair1=chair.mep_id)
order by mep.pg_chair_or_cochair1,
mep.mep_id;
The output of the SELECT Statement is the same as the SELF-JOIN Report.
This was an example of a SELF-JOIN with One Self-Referencing Foreign Key.
We now have an example of a SELF-JOIN with Two Self-Referencing Foreign Keys.
We create a report where we display NOT ONLY the Chair, BUT ALSO we display both Chairs, if the Political Group has Co-Chairs.
In this case, we access the table MEPS three times:
Join Condition for this report is seen below.
from meps mep join meps chair1 on (mep.pg_chair_or_cochair1=chair1.mep_id) left outer join meps chair2 on (mep.pg_cochair2=chair2.mep_id)
This Join Condition uses the two Two Self-Referencing Foreign Keys of the table MEPS.
select mep.first_name||' '||mep.last_name "MEP Name",
chair1.first_name||' '||chair1.last_name "Chair / Cohair 1 Name",
chair2.first_name||' '||chair2.last_name "Cohair 2 Name"
from meps mep
join meps chair1
on (mep.pg_chair_or_cochair1=chair1.mep_id)
left outer join meps chair2
on (mep.pg_cochair2=chair2.mep_id)
order by mep.pg_chair_or_cochair1, mep.mep_id;
In a SELECT Statement with two tables, when a Join Condition is omitted, when there is NO Join Condition, or when the Join Condition is Invalid, the query returns all combination of rows.
All rows in the first table are joined to all rows in the second table.
The result is a Cartesian Product of two Tables.
We have learned about Cartesian Product in "Chapter 1: A Relational Approach".
The section on Cartesian Product is under "Two Theoretical Languages / Relational Algebra / Product" of "Chapter 1: A Relational Approach".
The detailed definition of Cartesian Product below, is taken from that section.
For simplicity we take two tables with one column each and perform the PRODUCT Operation on them.
| T1_COLUMN1 |
|---|
| A |
| B |
| C |
TABLE_1 has 1 Column and 3 Rows.
| T2_COLUMN1 |
|---|
| 10 |
| 20 |
TABLE_2 has 1 Column and 2 Rows.
What will the Result of the PRODUCT Operation be?
If we call the Resulting Table of this PRODUCT Operation, TABLE_G, then the operation can be denoted by:
| T1_COLUMN1 | T2_COLUMN1 |
|---|---|
| A | 10 |
| A | 20 |
| B | 10 |
| B | 20 |
| C | 10 |
| C | 20 |
The Resulting Table of the PRODUCT Operation on TABLE_1 and TABLE_2, which is TABLE_G, has 6 Rows and 2 Columns.
Another Example on the PRODUCT Operation
Now, let us take the STAFF and the DEPARTMENTS tables, and perform the PRODUCT operation on these two tables.
STAFF table has 5 rows and 4 columns:
| staff id | name | dg id | program no |
|---|---|---|---|
| 802 | YILDIZ | 400 | 10 |
| 804 | HEYNEN | 400 | 20 |
| 806 | KANUITH | 400 | 30 |
| 808 | ZABOROWSKI | 400 | 40 |
| 1020 | OTTOVA | 400 | 60 |
DEPARTMENTS table has 3 rows and 2 columns:
| dg id | dg name |
|---|---|
| 200 | DG Communications |
| 400 | DG Innovation and Research |
| 600 | DG Legal Service |
Cartesian Product of these two tables, will produce a Result Table with 5x3=15 Rows, and 4+2=6 Columns as seen below:
| staff id | name | dg id | program no | dg id | dg name |
|---|---|---|---|---|---|
| 802 | YILDIZ | 400 | 10 | 200 | DG Communications |
| 802 | YILDIZ | 400 | 10 | 400 | DG Innovation and Research |
| 802 | YILDIZ | 400 | 10 | 600 | DG Legal Service |
| 804 | HEYNEN | 400 | 20 | 200 | DG Communications |
| 804 | HEYNEN | 400 | 20 | 400 | DG Innovation and Research |
| 804 | HEYNEN | 400 | 20 | 600 | DG Legal Service |
| 806 | KANUITH | 400 | 30 | 200 | DG Communications |
| 806 | KANUITH | 400 | 30 | 400 | DG Innovation and Research |
| 806 | KANUITH | 400 | 30 | 600 | DG Legal Service |
| 808 | ZABOROWSKI | 400 | 40 | 200 | DG Communications |
| 808 | ZABOROWSKI | 400 | 40 | 400 | DG Innovation and Research |
| 808 | ZABOROWSKI | 400 | 40 | 600 | DG Legal Service |
| 1020 | OTTOVA | 400 | 60 | 200 | DG Communications |
| 1020 | OTTOVA | 400 | 60 | 400 | DG Innovation and Research |
| 1020 | OTTOVA | 400 | 60 | 600 | DG Legal Service |
We have already seen the Definition of a Cartesian Product:
Cartesian Product will generate a large number of rows.
If a Cartesian Product is formed because of an Invalid Join Condition, or because a Join Condition was omitted by mistake, then it has to be corrected.
However, a Cartesian Product of Tables can be very useful.
I always used it myself on several occasions, when I needed test data.
Although the data created by the Cartesian Product can be meaningless, the fact that we have a large sum of rows and columns makes the Result Set very useful for tests with different purposes.
We will use the COMMITTEES table and the MEPS table in our Cartesian Product example.
In this example, there is NO JOIN Condition between the two tables.
Therefore a Cartesian Product is produced when we use this SELECT Statement:
SELECT * FROM COMMITTEES COMM JOIN MEPS M ON (1=1)
All rows in the COMMITTEES table are joined to all rows in the MEPS table.
Also, all columns in the COMMITTEES table and all columns in the MEPS table, are included as the columns of the Result Set.
We can see the SELECT Statements used and the output below.
SELECT COUNT(*)
FROM COMMITTEES COMM
--23
SELECT COUNT(*)
FROM MEPS M
-- 787
SELECT 23*787 FROM DUAL
-- 18101
SELECT COUNT(*)
FROM COMMITTEES COMM
JOIN MEPS M
ON (1=1)
--18101
SELECT *
FROM COMMITTEES COMM
JOIN MEPS M
ON (1=1)
The CROSS JOIN Clause produces a cross-product of the two tables, which is also a Cartesian Product.
Below, we can see the same example as in the Cartesian Product Example, only using the CROSS JOIN Clause this time.
SELECT COUNT(*)
FROM COMMITTEES COMM
CROSS JOIN MEPS M
--18101
SELECT M.*, COMM.*
FROM COMMITTEES COMM
CROSS JOIN MEPS M
This resumes the Chapter Multi Table Joins.
