• Skip to main content
  • Skip to primary sidebar

Datannum

Data science for small business applications

  • Home
  • Microsoft Excel (spreadsheet) forum
    • Project: Analyze NYSE Data
  • SQL forum
    • Project: Query a Digital Music Store Database
  • Tableau forum (data visualization)
    • Project: Data Visualization Project using Tableau
  • Linear Algebra forum
    • Curated resource to learn linear algebra
  • Web development
    • WordPress forum
    • Google Support

Self Join: What this query is doing

Data Science for Small Business Applications › Forums › SQL › Self Join: What this query is doing

Tagged: SELF JOIN in SQL

  • This topic is empty.
Viewing 1 post (of 1 total)
  • Author
    Posts
  • April 21, 2022 at 12:22 pm #139
    Admin
    Keymaster

      Self join SQL https://www.tutorialspoint.com/sql/sql-self-joins.htm

      Clueless what this query is doing:

      SQL> SELECT  a.ID, b.NAME, a.SALARY
         FROM CUSTOMERS a, CUSTOMERS b
         WHERE a.SALARY < b.SALARY;

      I understand self join is creating two temporary tables out of the same table.

      In the above query, two temporary tables (with aliases a and b) created out of same table CUSTOMERS. While ID and SALARY are retrieved from a, NAME from b.

      Beyond that, an explanation of the above query will be helpful. Thanks in advance.


      Reply


      This is how the comparison happens: first row of a compared with all the rows of b, then second row of a compared with all the rows of b and so on… Cartesian product. https://www.tutorialspoint.com/sql/sql-cartesian-joins.htm

      The join creating one giant table out of the table. It is comparing all the rows with all the rows, and any time b.salary is more than a.salary, it’s returning the id and salary of the lower paid individual and the name of the higher paid individual.

      All the other rows where the salaries are the same or b lower get tossed from the results.

       

    • Author
      Posts
    Viewing 1 post (of 1 total)
    • You must be logged in to reply to this topic.
    Log In

    Primary Sidebar

    profile for Techcosec Limited on Stack Exchange, a network of free, community-driven Q&A sites

    RSS CS50’s Introduction to Computer Science by Harvard University on Edx

    • How to print winner January 21, 2023
    • find_min function and infinite loop when a candidate not winner in first instance January 17, 2023
    • Bool variable won and print_winner function January 12, 2023
    • True condition with while loop January 6, 2023
    • How to finally display result in tabular form of choices entered by voters December 12, 2022

    Subscribe via Email

    Enter your email address to subscribe and receive notifications of new posts by email.

    This website hosted on WP Engine

    Initiative by Digital Splendid OPC Pvt. Ltd. & Techcosec Limited