# Informatics Practices for Class XI (CBSE)/Joins and Subqueries
### Joins

The Most important aspect of SQL is its relational features. You can query, compare and calculate two different tables having entirely different structure. Joins and subselects are the two methods to join tables. Both methods of joining tables should give the same results. The natural join is faster on most SQL platforms.
In the following example a student is trying to learn what the numbers are called in hindi.
| | select * from english | | select * from hindi |
| Tag | Inenglish | Tag | Inhindi |
| 1 | One | 2 | Do |
| 2 | Two | 3 | Teen |
| 3 | Three | 4 | Char |

#### Cartesian join

A Cartesian join is when you join every row of one table to every row of another table.
It is also called Cross Join and may be written in this way:
| Tag | In english | Tag | In hindi |
| 1 | One | 2 | Do |
| 2 | Two | 3 | Teen |
| 3 | Three | 4 | Char |

#### Inner Join

| Tag | Inenglish | Inhindi |
| 2 | Two | Do |
| 3 | Three | Teen |
You can also write the same query as
Natural Joins using "using" (Compatible: MySQL 4+; but changed in MySQL 5) The following statement using "USING" method will display the same results.

#### Outer Joins

| Tag | Inenglish | Tag | Inhindi |
| 1 | One | | |
| 2 | Two | 2 | Do |
| 3 | Three | 3 | Teen |
| | | 4 | Char |

#### LEFT JOIN / LEFT OUTER JOIN

The syntax is as follows:

#### Right Outer Join

English tag Hindi --no row-- 4 Char
* Make sure that you have the same name and same data type in both tables.
* The keywords LEFT and RIGHT are not absolute, they only operate within the context of the given statement: we can reverse the order of the tables and reverse the keywords, and the result would be the same.
* If the type of join is not specified as inner or outer then it will be executed as an INNER JOIN.

#### Full Outer Join

As for v5.1, MySQL does not provide FULL OUTER JOIN. You may emulate this using a series of UNIONed SELECT statements.

#### Multiple joins

It is possible to join more than just two tables:
Here is an example from Savane:

### Subqueries

(Compatible: Mysql 4.1 and later...Bold text)
* SQL subqueries let you use the results of one query as part of another query.
* Subqueries are often natural ways of writing a statement.
* Let you break a query into pieces and assemble it.
* Allow some queries that otherwise can't be constructed. Without using a subquery, you have to do it in two steps.
* Subqueries always appear as part of the WHERE (or HAVING) clause.
* Only one field can be in the subquery SELECT. It means Subquery can only produce a single column of data as its result.
* ORDER BY is not allowed; it would not make sense.
* Usually refer to name of a main table column in the subquery.
* This defines the current row of the main table for which the subquery is being run. This is called an outer reference.
For e.g. If RepOffice= OfficeNbr from Offices table, list the offices where the sales quota for the office exceeds the sum of individual salespersons' quotas
??? is the sum of the quotas of the salespeople, i.e.
We combine these to get
Display all customers with orders or credit limits > $50,000. Use the DISTINCT word to list the customer just once.