How to use JOIN statement in MySQL

[Atsushi]

I know that the JOIN statement is probably the most widely used in SQL, because it is one that allows us to perform the essential work that needs to do a database, retrieve data that is stored. Often the need to select data collected from multiple tables, obviously related. But I am not aware about it in details. So thought to take some help from you scholars. I want to clear my base (basic things) before going deep into this programming language. So I am expecting some notes from your side about the JOIN statement that are used in MySQL. Please help me by providing essential information.

[Pratyush]

There are different types of joins. Basically there are three: the inner join, the outer join and cross join. The cross join is conceptually the simplest, but is rarely applied because it is difficult to have a logical sense: it is the "cartesian product" of two tables. Virtually every line of the first table is combined with all rows in the second. Assuming you have a table of 5 rows and 6, the result will be a table of 30 lines. The inner join is performed going to try matching rows in two tables, based on the value of certain columns. Imagine, in a classic example, have a table and orders a customer table. We say that the first column contains the OrderID, the CustomerID, item, quantity, while the second contains the CustomerID, name, surname. Evidently the field 'CustomerID' in the table orders is a foreign key on the Customers table, which allows us to recover the data of the customer who placed the order (we have kept to a minimum, for simplicity, the number of fields in the two tables ). In this case, then we can do join relying on matching field values 'CustomerID' in the two tables (of course it is not necessary that the columns have the same name). The lines are drawn with an inner join only those that have the value of a table corresponding to the other table. The outer join, like inner join, are carried out according to the correspondence of some values in the tables. The difference is that in the case of outer join, you can also remove rows from a table that does not have correspondents in the other.

[Samsher]

I think that example will help you to understand better. Some examples:

AS SELECT * FROM orders or customers AS c WHERE AND orderId c.idClient o.idClient => 1000; 
SELECT * FROM customers JOIN orders AS or AS c WHERE orderId c.idClient on o.idClient => 1000;

These two queries are equivalent, representing an inner join: extract data on orders and customer orders for those who have an ID greater than 1000. The first is an implicit join: in fact we have not explicitly stated, and we put the join condition in the WHERE clause. When more than one table in the FROM list without declaring explicitly JOIN are doing an inner join (or a cross join unless specified join conditions in WHERE). In the second, instead of 'JOIN' we could write out 'INNER JOIN'; in some older versions of MySQL that is required.

SELECT * FROM orders as customers or as c LEFT JOIN ON WHERE orderId c.idClient o.idClient => 1000;

In this case, we performed a left outer join: The query gets the same results as the previous two, but most will return any rows in the Orders table where the CustomerID value does not correspond to the Customers table. In these rows in the result table, the fields will be valued in the other table to NULL. So we could run a query that extracts only the first rows of the table without a corresponding way:

SELECT * FROM orders as customers or as c LEFT JOIN ON o.idClient = c.idClient 
WHERE orderId> 1000 IS NULL AND c.idClient

[LasitMalinga]

The outer join is divided into left outer join, right outer joins and full outer joins. With the first we get the rows without matching found in the left table (the one declared first in the query). The right outer join, however, return the rows in the second table that have no counterpart in the first. With the full outer join then you get rows from both tables without the corresponding. In the syntax of MySQL, the word OUTER is optional: write LEFT JOIN or LEFT OUTER JOIN is equivalent. Similarly we could write RIGHT JOIN or RIGHT OUTER JOIN to a RIGHT JOIN.

[Reckon]

So far you have seen examples of join between two tables, but you can also among more than two. In this case the operation is logically divided into a join, each of which is made between two tables, the result of each join becomes one of the two tables involved in join later. The order in which they take different join depends on the order in which lists and tables (since MySQL 5.0.1) use any parentheses:

FROM t1 JOIN t2 ON t1.col1 t2.col2 = LEFT JOIN t3 ON t2.col3 = t3.col3

In case this is done before the join between t1 and t2, but here, the result of this is used to join with the left join t3.

FROM t1 JOIN (t2 LEFT JOIN t3 ON t2.col3 t3.col3 =) = ON t1.col1 t2.col2

The presence of parentheses causes to be made before the left join between t2 and t3, and then the result is used for the inner join with t1.