MYSQL JOINS
Join
A join is a query that combines rows from two or more tables. In a join query, more than one tables are listed in FROM clause. The function of combining data from multiple tables is called joining. SQL can obtain data from several related tables by performing either a physical or virtual join on the tables.
Joins are used when we have to select data from two or more tables. Joins are used to extract data from two (or more) tables, when we need a relationship between certain columns
in these tables.
The SQL Join condition is always used in the WHERE clause of SELECT, UPDATE and DELETE statements.
There are different kind of SQL joins are as follows:
1. Cartesian Product (Cross Product)
2. Equi Join
3. Self Join
4. Non-Equi Join
5.Natural Join
1. Cartesian product:
It is known as cross join. The number of tuples in new relation is equal to product of number of tuples of the two tables on which Cartesian product is performed.
SELECT Name, Amount FROM CUSTOMER, BORROWER;
Cross Join (Cartesian Product)
The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of
result is called as Cartesian Product.
Pictorial Presentation of Cross Join syntax
Equi join is a simple SQL join condition that uses equal sign
as a comparison operator.
Syntax SELECT coll. c012. c013
FROM tablel. table?
KHERE tablel.coll – table2.c011:
mysql> select * from emp;
+——-+——–+———–+——+————+———+———+——–+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+——-+——–+———–+——+————+———+———+——–+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+——-+——–+———–+——+————+———+———+——–+