• 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

Types of Joins in SQL and what a particular instance will fall into

Data Science for Small Business Applications › Forums › SQL › Types of Joins in SQL and what a particular instance will fall into

Tagged: JOINS in SQL

  • This topic is empty.
Viewing 1 post (of 1 total)
  • Author
    Posts
  • April 23, 2022 at 9:30 am #143
    Admin
    Keymaster

      TypesofJoinsinSQL Fig 1 – Source: https://www.w3schools.com/sql/sql_join.asp

      Here is the problem that I am solving:

      All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

      Find the name and ID of the artists who do not have albums.

      The albums table seems subset of artists table in the sense that artistsId included in albums table will be included in the artists table. At the same time, there will be artists not having any albums.

      What kind of JOIN will the above two tables will have relationship into?

      The applicable case I could see is the RIGHT JOIN (fig 1), but the result should not display the green part (table2 ), instead white part (table1).


      Reply


      Here’s a tip: https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/.

      Here’s another tip: never use RIGHT joins, always use LEFT joins instead.

      the result should not display the green part (table2 ), instead white part (table1).

      you’re looking for an anti-join

      This is the code that might work here:

      SELECT Artists.ArtistId, Artists.Name, Albums.Title
      FROM Artists
      LEFT JOIN Albums
      ON Artists.ArtistId = Albums.ArtistId
      WHERE Albums.Title IS NULL

      Why selecting albums.title – you know that one’s null for any records selected. So why have it at all?

      I included that for my own confirmation that getting null under Title. Yes, should be removed after that.

      Depending on the size of the dataset, one may have just done a where condition subquery for not in the album table to avoid a join. Again, just depends on data scale plus where your indices exist.

      SELECT Name, Artists.ArtistId
      FROM Artists
      WHERE NOT EXISTS
      (SELECT ArtistId
      FROM Albums
      WHERE artists.artistid = albums.artistid)
      
      
      
    • 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