MySQL – Join

When you run queries instead of JOINS, the same task can be accomplished. If you have experience with database programming, you know that we can run queries one by one, utilizing the output of each in the next query. Yes, it is possible. Using JOINS, you can get the job done by executing only one query with any search parameters. The use of indexing in MySQL, however, allows for better performance with JOINS. Using a single JOIN query rather than multiple queries reduces server overhead. Instead of executing multiple queries, more data is transferred from MySQL to applications (software). Furthermore, it requires more data manipulation at the application level as well.

• Data from multiple tables can be combined using JOINS.
• JOINS perform better than subqueries
• Rows matching the given criteria are returned only by INNER JOINS.
• A JOIN WITH OUTER JOIN can also return rows with no matches. Unmatched rows are returned as NULL.
• A major JOIN type is an inner join, followed by a left outer or right outer join, and then a cross join.
• In JOIN operations, “ON” is frequently used. The “USING” clause requires that matching columns have the same name.
• JOINS can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, and AGGREGATE FUNCTIONS.

Types of joins

• Cross JOIN
• INNER JOIN
• OUTER JOIN
• LEFT JOIN
• RIGHT JOIN