{"id":4948,"date":"2015-12-02T10:00:57","date_gmt":"2015-12-02T01:00:57","guid":{"rendered":"http:\/\/www.skyarch.net\/blog\/?p=4948"},"modified":"2015-12-01T17:37:20","modified_gmt":"2015-12-01T08:37:20","slug":"learn-sql-sub-query-in-easy-way","status":"publish","type":"post","link":"https:\/\/www.skyarch.net\/blog\/en\/learn-sql-sub-query-in-easy-way\/","title":{"rendered":"Learn SQL Sub-Query in Easy Way"},"content":{"rendered":"

SQL Sub-Query<\/strong><\/span><\/h1>\n
\n

\u27a3<\/span>\u00a0 Sub-queries are queries embedded in queries.<\/h3>\n

\u27a3<\/span>\u00a0 They are used to retrieve data from one table based on data in another table.<\/h3>\n

\u27a3<\/span>\u00a0 Generally used when tables have some kind of relationship.<\/h3>\n

\u27a3<\/span>\u00a0 Meaning a sub-query SELECT statement can standalone and is NOT\u00a0depended on the statement in which it is nested.<\/h3>\n<\/blockquote>\n

The sub-query can contain any valid SELECT statement, but it must return a single column with the expected number of results.<\/strong><\/p>\n

IF<\/strong>\u00a0the sub-query returns only one result, then the main query can check for equality, inequality, greater than, less than, etc.
\nOn the other hand, if the subquery returns more than one record, the main query must check to see if a field value is (or is NOT<\/strong> ) IN<\/strong> the set of values returned.<\/p>\n

FOR<\/strong>\u00a0example in database, the Orders Table<\/u><\/strong> has a customer_id<\/i> field, which references a customer in the Customers Table<\/u><\/strong>.\u00a0Thus, retrieving data for a specific order is pretty straightforward.<\/p>\n

 <\/p>\n\n\n\n\n\n\n\n\n
id<\/strong><\/th>\nfirst_name<\/strong><\/th>\nlast_name<\/strong><\/th>\nage<\/strong><\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\njohn<\/td>\nwall<\/td>\n24<\/td>\n<\/tr>\n
2<\/td>\nmark<\/td>\nsmith<\/td>\n34<\/td>\n<\/tr>\n
3<\/td>\njames<\/td>\nlee<\/td>\n31<\/td>\n<\/tr>\n
4<\/td>\nshiela<\/td>\nmaye<\/td>\n22<\/td>\n<\/tr>\n<\/tbody>\n
CUSTOMERS TABLE<\/strong><\/caption>\n<\/table>\n\n\n\n\n\n\n\n\n
id<\/strong><\/th>\ncustomer_id<\/strong><\/th>\nproduct<\/strong><\/th>\ncategory<\/strong><\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n4<\/td>\ncasserole<\/td>\nkitchen<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\ngrass cutter<\/td>\ngarage<\/td>\n<\/tr>\n
3<\/td>\n1<\/td>\nwiper<\/td>\ngeneral<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\nrice cooker<\/td>\nelectronic<\/td>\n<\/tr>\n<\/tbody>\n
ORDERS TABLE<\/strong><\/caption>\n<\/table>\n

Detailed Examples<\/strong> :<\/p>\n

Note:<\/strong>
\nc<\/b> = alias for customers
\no<\/b> = alias for orders<\/p>\n

\u2666 Let's start with a simple SQL Left Join Query.<\/strong><\/h3>\n

SELECT *\u00a0FROM\u00a0customers c
\nLEFT JOIN orders o
\non c.id = o.customer_id
\nWHERE\u00a0o.product = 'wiper';<\/p>\n

Step by Step Process<\/strong> :<\/p>\n

    \n
  1. Selecting all the data from Customers Table with matched<\/strong> data in Orders Table (see 1-a Table)<\/strong>.<\/li>\n
  2. Joining\/merging the data from Orders Table where ID (customers table) is EQUAL to CUSTOMER_ID (orders table) (see 2-a Table)<\/strong>.<\/li>\n
  3. Filter the results by (WHERE) clause, product<\/strong> (orders table) is EQUAL<\/strong> to ' wiper<\/strong> ' (see 3-a Table)<\/strong>.<\/li>\n<\/ol>\n\n\n\n\n\n\n\n
    id<\/strong><\/th>\nfirst_name<\/strong><\/th>\nlast_name<\/strong><\/th>\nage<\/strong><\/th>\n<\/tr>\n<\/thead>\n
    1<\/td>\njohn<\/td>\nwall<\/td>\n24<\/td>\n<\/tr>\n
    2<\/td>\nmark<\/td>\nsmith<\/td>\n34<\/td>\n<\/tr>\n
    4<\/td>\nshiela<\/td>\nmaye<\/td>\n22<\/td>\n<\/tr>\n<\/tbody>\n
    1-a Table<\/strong>:<\/caption>\n<\/table>\n\n\n\n\n\n\n\n\n
    id<\/strong><\/th>\nfirst_name<\/strong><\/th>\nlast_name<\/strong><\/th>\nage<\/strong><\/th>\ncustomer_id<\/strong><\/th>\nproduct<\/strong><\/th>\ncategory<\/strong><\/th>\n<\/tr>\n<\/thead>\n
    1<\/td>\njohn<\/td>\nwall<\/td>\n24<\/td>\n1<\/td>\nwiper<\/td>\ngeneral<\/td>\n<\/tr>\n
    1<\/td>\njohn<\/td>\nwall<\/td>\n24<\/td>\n1<\/td>\nrice cooker<\/td>\nelectronic<\/td>\n<\/tr>\n
    2<\/td>\nmark<\/td>\nsmith<\/td>\n34<\/td>\n2<\/td>\ngrass cutter<\/td>\ngarage<\/td>\n<\/tr>\n
    4<\/td>\nshiela<\/td>\nmaye<\/td>\n22<\/td>\n4<\/td>\ncasserole<\/td>\nkitchen<\/td>\n<\/tr>\n<\/tbody>\n
    2-a Table<\/strong>:<\/caption>\n<\/table>\n\n\n\n\n\n
    id<\/strong><\/th>\nfirst_name<\/strong><\/th>\nlast_name<\/strong><\/th>\nage<\/strong><\/th>\ncustomer_id<\/strong><\/th>\nproduct<\/strong><\/th>\ncategory<\/strong><\/th>\n<\/tr>\n<\/thead>\n
    1<\/td>\njohn<\/td>\nwall<\/td>\n24<\/td>\n1<\/td>\nwiper<\/td>\ngeneral<\/td>\n<\/tr>\n<\/tbody>\n
    3-a Table - FINAL RESULT<\/strong>:<\/caption>\n<\/table>\n

    CONCLUSION<\/strong>:<\/b> Our first query gathers all the data first by executing the SELECT<\/strong>, JOIN<\/strong> functions, then by setting the WHERE<\/strong> clause at the end of query will filter the data given by the first executed functions.<\/p>\n

    <\/h3>\n

    \u2666 Next, let's do the SQL Join with Sub-Query.<\/strong><\/h3>\n

    SELECT * FROM\u00a0customers c
    \nLEFT JOIN (\u00a0SELECT * FROM\u00a0orders o WHERE\u00a0o.product = 'wiper'\u00a0) as sub_result
    \non c.id = sub_result.customer_id;<\/p>\n

    Step by Step Process<\/strong> :<\/p>\n

      \n
    1. Selecting all the data from Customers Table with matched<\/strong> data in Orders Table (see 1-b Table)<\/strong>.<\/li>\n
    2. Selecting all the data from Orders Table where product<\/strong> (orders table) is EQUAL<\/strong> ' wiper<\/strong> ' (see 2-b Table)<\/strong>.<\/li>\n
    3. Joining\/merging data from Step1 and Step2 with filter clause (see 3-b Table)<\/strong> .
      \n3-1: Filter the results by (WHERE) clause statement.
      \n3-2. All data from Step1 will remain, appending the data from Step2 if matched, id<\/strong> (customers table) is EQUAL<\/strong> to customer_id<\/strong> (orders table).
      \n3-3. All data from Step1 with NO matched, will append and set to null value<\/strong> .<\/li>\n<\/ol>\n\n\n\n\n\n\n\n
      id<\/strong><\/th>\nfirst_name<\/strong><\/th>\nlast_name<\/strong><\/th>\nage<\/strong><\/th>\n<\/tr>\n<\/thead>\n
      1<\/td>\njohn<\/td>\nwall<\/td>\n24<\/td>\n<\/tr>\n
      2<\/td>\nmark<\/td>\nsmith<\/td>\n34<\/td>\n<\/tr>\n
      4<\/td>\nshiela<\/td>\nmaye<\/td>\n22<\/td>\n<\/tr>\n<\/tbody>\n
      1-b Table<\/strong>:<\/caption>\n<\/table>\n\n\n\n\n\n
      id<\/strong><\/th>\ncustomer_id<\/strong><\/th>\nproduct<\/strong><\/th>\ncategory<\/strong><\/th>\n<\/tr>\n<\/thead>\n
      3<\/td>\n1<\/td>\nwiper<\/td>\ngeneral<\/td>\n<\/tr>\n<\/tbody>\n
      2-b Table<\/strong>:<\/caption>\n<\/table>\n\n\n\n\n\n\n\n
      id<\/strong><\/th>\nfirst_name<\/strong><\/th>\nlast_name<\/strong><\/th>\nage<\/strong><\/th>\ncustomer_id<\/strong><\/th>\nproduct<\/strong><\/th>\ncategory<\/strong><\/th>\n<\/tr>\n<\/thead>\n
      1<\/td>\njohn<\/td>\nwall<\/td>\n24<\/td>\n1<\/td>\nwiper<\/td>\ngeneral<\/td>\n<\/tr>\n
      2<\/td>\nmark<\/td>\nsmith<\/td>\n34<\/td>\nnull<\/td>\nnull<\/td>\nnull<\/td>\n<\/tr>\n
      4<\/td>\nshiela<\/td>\nmaye<\/td>\n22<\/td>\nnull<\/td>\nnull<\/td>\nnull<\/td>\n<\/tr>\n<\/tbody>\n
      3-b Table - FINAL RESULT<\/strong>:<\/caption>\n<\/table>\n

      CONCLUSION<\/strong>:<\/b> Our second query gets all the data from the Customers Table, but before joining the result from Orders Table there's another function called SUB-QUERY (a independent\/standalone query) which filters the data and sending it to a variable which allows us to be used for further execution like join function.<\/p>\n

      Happy Programming Everyone !!!\u00a0\"smiley_skyarch\"<\/p>\n","protected":false},"excerpt":{"rendered":"

      SQL Sub-Query \u27a3\u00a0 Sub-queries are queries embedded in queries. \u27a3\u00a0 They are used to retrieve data from one table…<\/p>\n","protected":false},"author":1,"featured_media":4940,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_locale":"en_US","_original_post":"4948","footnotes":""},"categories":[24,23],"tags":[],"class_list":{"0":"post-4948","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-mysql","8":"category-23","9":"en-US"},"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts\/4948","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/comments?post=4948"}],"version-history":[{"count":20,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts\/4948\/revisions"}],"predecessor-version":[{"id":4974,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/posts\/4948\/revisions\/4974"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/media\/4940"}],"wp:attachment":[{"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/media?parent=4948"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/categories?post=4948"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skyarch.net\/blog\/wp-json\/wp\/v2\/tags?post=4948"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}