Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 4: SQL Joins
Cartesian Product:
If in the joins statement you forget to put the WHERE condition or
intentionally don’t write the WHERE clause at all, the result will be
Cartesian product. Such type of output will be very rarely useful. If
one table had five records and other one had six records; in the output
you will get thirty records (5x6=30). That’s why we use the word product
with such type of join.
Equi-Join:
In the word Equijoin, Equi comes because we use Equal sign (=) in the
join condition. The query containing Equijoin will give you total number
of records equal to or less than the number of records of the one table
among all the tables in the query, having least number of records in it.
Figure 9: Equijoin/Simple Join/Inner Join: The
equality (=) sign is used to join two tables.
________________________________________
Lets take a example of two table Equijoin query like
the one shown in the slide, only those records will be displayed from
either of two tables where there is an exact match between the columns
used in the WHERE clause.
Example:
SELECT ename, job, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;
Or if you want to further reduce the number of records in the output you
can extend the filter in the WHERE clause just like the example
mentioned below. In the WHERE clause there are two conditions on both
sides of AND operator, only those records will be displayed that satisfy
both of these conditions.
Example:
SELECT ename, job, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND job = 'CLERK';
Output:
ENAME JOB DEPTNO DNAME
---------- --------- ---------- --------------
MILLER CLERK 10 ACCOUNTING
SMITH CLERK 20 RESEARCH
ADAMS CLERK 20 RESEARCH
JAMES CLERK 30 SALES
Chapter 4: SQL Joins
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: