SQL Join

Ever seen this type of query for join:

select c.variant,c.unit,c.price,c.sku, a.freshness, b.*, d.parent_id, d.id, d.slug From product_catalogs a,             product_catalog_translations b,
product_price_lists c,
product_categories d
where a.id=b.product_catalog_id
and b.locale='en'
and a.id=c.product_catalog_id
and a.deleted_at is null
and a.is_archive=0
and d.id = a.product_category_id;

What would you call this type of sql query? I normally use inner join, left join or right join to join table. Yet, this method does not work when you want to query database to export join data in excel.

ref: http://www.sql-join.com/sql-join-types

select a.product_category_id,a.name,b.parent_id,c.name from product_category_translations a,
product_categories b,
(select product_category_id,name from product_category_translations a,
product_categories b
where a.locale='en'
and a.product_category_id=b.id
and b.parent_id is null) c
where a.locale='en'
and a.product_category_id=b.id
and b.parent_id=c.product_category_id
;

Ever seen this kind of query ? It has bracket in side of the query which I do not understand what is it for and what it means?

SlimShady

6 years ago

Level 9

You do JOINs in this query, but it is implicit because you select from multiple tables.
What happens here is called subquery (query within a query). It's pretty useful and sometimes can make things less complex. You can read more about it here: https://dev.mysql.com/doc/refman/8.0/en/subqueries.html

davy_yg

6 years ago

Level 27

I don't really understand what is the point using sub queries such as:

SQL> SELECT * 
    FROM CUSTOMERS 
    WHERE ID IN (SELECT ID 
        FROM CUSTOMERS 
        WHERE SALARY > 4500) ;

ref: https://www.tutorialspoint.com/sql/sql-sub-queries.htm

Where you can actually write:

SQL> SELECT * 
    FROM CUSTOMERS 
    WHERE SALARY > 4500;

I also have one case such as: I want to show all products for example. These products actually have many sub titles. Can you actually print all the products and at the same time grouping them based on the subtitles (printing the subtitle at the same time).

Note: the subtitle group must be printed once only for the same subtitle. How to write query like this? Do we need a nested query ?

Please or to participate in this conversation.