[mysql] How to use mysql JOIN without ON condition?

Is it possible to write join query without ON statement? and how do these joins differ LEFT JOIN, RIGHT JOIN works.

This question is related to mysql join left-join inner-join right-join

The answer is


See some example in http://www.sitepoint.com/understanding-sql-joins-mysql-database/

You can use 'USING' instead of 'ON' as in the query

SELECT * FROM table1 LEFT JOIN table2 USING (id);

There are several ways to do a cross join or cartesian product:

SELECT column_names FROM table1 CROSS JOIN table2;

SELECT column_names FROM table1, table2;

SELECT column_names FROM table1 JOIN table2;

Neglecting the on condition in the third case is what results in a cross join.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to join

Pandas Merging 101 pandas: merge (join) two data frames on multiple columns How to use the COLLATE in a JOIN in SQL Server? How to join multiple collections with $lookup in mongodb How to join on multiple columns in Pyspark? Pandas join issue: columns overlap but no suffix specified MySQL select rows where left join is null How to return rows from left table not found in right table? Why do multiple-table joins produce duplicate rows? pandas three-way joining multiple dataframes on columns

Examples related to left-join

MySQL select rows where left join is null How to return rows from left table not found in right table? How to specify names of columns for x and y when joining in dplyr? Select rows which are not present in other table SQL LEFT-JOIN on 2 fields for MySQL Combine two pandas Data Frames (join on a common column) SQL LEFT JOIN Subquery Alias How to use mysql JOIN without ON condition? LEFT JOIN only first row MySQL - sum column value(s) based on row from the same table

Examples related to inner-join

Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working Multiple INNER JOIN SQL ACCESS How to select all rows which have same value in some column Eliminating duplicate values based on only one column of the table How can I delete using INNER JOIN with SQL Server? How to use mysql JOIN without ON condition? Inner join with 3 tables in mysql SQL Inner join more than two tables MySQL INNER JOIN select only one row from second table Insert using LEFT JOIN and INNER JOIN

Examples related to right-join

How to use mysql JOIN without ON condition? Difference between left join and right join in SQL Server