One of the most time-consuming operations in a database is the JOIN. We use this when we need to join two or more tables due to the normalized structure of the database. There are many types of joins (equi-join, self-join, outer join, anti-join, and so on).
In this recipe, we will see some join algorithms the database can use to answer our queries, performance related to every type of join, and some tricks to avoid joins (when possible).
The following steps will demonstrate some common types of joins:
Connect to the
SHschema:CONNECT sh@TESTDB/shCreate a table called
MY_CUSTOMERSas a copy of theCUSTOMERStable:CREATE TABLE sh.MY_CUSTOMERS AS SELECT * FROM sh.CUSTOMERS; ALTER TABLE sh.MY_CUSTOMERS ADD CONSTRAINT PK_MY_CUSTOMERS PRIMARY KEY (CUST_ID);
Create a table called
MY_COUNTRIESas a copy of theCOUNTRIEStable:CREATE TABLE sh.MY_COUNTRIES AS SELECT * FROM sh.COUNTRIES; ALTER TABLE sh.MY_COUNTRIES ADD CONSTRAINT PK_MY_COUNTRIES PRIMARY KEY (COUNTRY_ID...