MySQL OUTER JOIN syntax error

Revati S Misra 1094 27 Jul 2023

Revati S Misra

Student

Skilled in SEO, content writing, and digital marketing. Completed several years of working in many organizations including multinational companies. I love to learn new things in life that keep me motivated.

1 Answers

Aryan Kumar 28 Jul 2023

MySQL does not support the OUTER JOIN keyword. Instead, you can use the LEFT JOIN, RIGHT JOIN, or FULL JOIN keywords to achieve the same results.

The LEFT JOIN keyword returns all rows from the left table, even if there are no matching rows in the right table. The RIGHT JOIN keyword returns all rows from the right table, even if there are no matching rows in the left table. The FULL JOIN keyword returns all rows from both tables, even if there are no matching rows in the other table.

For example, the following SQL statement will use a LEFT JOIN to return all products, even if they do not have a corresponding category:

SQL

SELECT *
FROM products
LEFT JOIN categories
ON products.category_id = categories.id;

This will return all rows from the products table, even if there are no matching rows in the categories table. The categories table will only be included in the results if there is a matching row in the products table.

Here is an example of how to use the LEFT JOIN keyword in MySQL:

SQL

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price INT NOT NULL,
  category_id INT,
  PRIMARY KEY (id)
);

CREATE TABLE categories (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO products (name, price, category_id) VALUES
('Product 1', 100, 1),
('Product 2', 200, 2),
('Product 3', 300, NULL);

INSERT INTO categories (name) VALUES
('Category 1'),
('Category 2');

SELECT *
FROM products
LEFT JOIN categories
ON products.category_id = categories.id;

This will return the following result:

id | name | price | category_id | name
------+------+-------+--------------+
1 | Product 1 | 100 | 1 | Category 1
2 | Product 2 | 200 | 2 | Category 2
3 | Product 3 | 300 | NULL | NULL

As you can see, the LEFT JOIN keyword has returned all rows from the products table, even if they do not have a corresponding category. The categories table will only be included in the results if there is a matching row in the products table.

Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md

forum

MySQL OUTER JOIN syntax error

Can you answer this question?

1 Answers