Answer to Question #273268 in Databases | SQL | Oracle | MS Access for Ampfarisaho

Question

Using your code examples, explain what the following are and when you would use them:



A subquery.



A Join.

Answer 1

Subqueries are used in complex SQL queries. Usually, there is a main outer query and one or more subqueries nested within the outer query.

SELECT name, cost
FROM product
WHERE id=(SELECT product_id
  FROM sale
    WHERE price=2000
    AND product_id=product.id
  );

The JOIN clause does not contain additional queries. It connects two or more tables and selects data from them into a single result set. It is most frequently used to join tables with primary and foreign keys.

SELECT p.name, p.cost
FROM product p
JOIN sale s ON p.id=s.product_id
WHERE s.price=2000;