SQL joining through referential integrity
Referential Integrity
A REFERENTIAL INTEGRITY is a database concept that is used to build and maintain logical relationships between tables to avoid logical corruption of data. It is a very useful and important part in RDBMS.
Usually, referential integrity is made up of the combination of a primary key and a foreign key.
The main concept of REFERENTIAL INTEGRITY is that it does not allow to add any record in a table that contains the foreign key unless the reference table containing a corresponding primary key.
If any record in referenced table (i.e. the table who contain primary key) is deleted, all the corresponding records in the referencing table will be deleted for the referential integrity.
Example:
Sample table: agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
...........
To get 'agent_code' and 'agent_name' from 'agents' table and 'ord_num' and 'advance_amount' form 'orders' after a joining with following conditions -
1. 'agent_code' is primary key in 'agents' table
2. 'agent_code' is a foreign key in orders table which is referring to the primary key of 'agents' table.
3. 'agent_code' of 'agents' and 'orders' must be same.
the following SQL statement can be used:
SQL Code:
-- Selecting specific columns: 'agent_code' and 'agent_name' from the 'agents' table, and 'ord_num' and 'advance_amount' from the 'orders' table
SELECT agents.agent_code, agents.agent_name, orders.ord_num, orders.advance_amount
-- Performing a Cartesian product (cross join) between the 'agents' and 'orders' tables (implicit join)
FROM agents, orders
-- Defining the join condition in the WHERE clause where 'agents.agent_code' equals 'orders.agent_code'
WHERE agents.agent_code = orders.agent_code;
Explanation:
- This SQL query retrieves data from two tables: 'agents' and 'orders'.
- It selects specific columns from these tables: 'agent_code' and 'agent_name' from the 'agents' table, and 'ord_num' and 'advance_amount' from the 'orders' table.
- Instead of using explicit JOIN syntax, this query uses a Cartesian product (cross join) by listing the tables in the FROM clause separated by a comma. This results in combining every row from the 'agents' table with every row from the 'orders' table.
- The join condition is specified in the WHERE clause, where 'agents.agent_code' must equal 'orders.agent_code'. This filters the result to include only rows where the agent codes match between the two tables.
- While this query produces the desired result, it's important to note that it may be less efficient than using explicit JOIN syntax, especially on large tables. Explicit JOINs provide better readability and maintainability of the query.
- This query is useful for retrieving information about agents and their corresponding orders, linking the two tables based on the agent code.
Output:
AGENT_CODE AGENT_NAME ORD_NUM ADVANCE_AMOUNT ---------- ---------------------------------------- ---------- -------------- A008 Alford 200114 2000 A004 Ivan 200122 400 A006 McDen 200118 100 A010 Santakumar 200119 700 A004 Ivan 200121 600 A011 Ravi Kumar 200130 400 A005 Anderson 200134 1800 A004 Ivan 200108 600 A005 Anderson 200103 700 ................. .................
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SQL SELF JOIN
Next: Joining tables with group by and order by