forum

home / developersection / forums / mysql outer join syntax error

Ask Question
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.

Follow
Can you answer this question?
Answer

1 Answers

Aryan Kumar

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.

advertising
advertising

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy