Database Essentials using Postgres - Writing Basic SQL Queries - Joining Tables - Inner

Databases Postgres
1

Let us understand how to join data from multiple tables.

  • We will primarily focus on ANSI style join (JOIN with ON).

  • There are different types of joins.

    • INNER JOIN - Get all the records from both the datasets which satisfies JOIN condition.
    • OUTER JOIN - We will get into the details as part of the next topic

  • Example for INNER JOIN

SELECT o.order_id,
    o.order_date,
    o.order_status,
    oi.order_item_subtotal
FROM orders o JOIN order_items oi
    ON o.order_id = oi.order_item_order_id
LIMIT 10
  • We can join more than 2 tables in one query. Here is how it will look like.
SELECT o.order_id,
    o.order_date,
    o.order_status,
    oi.order_item_subtotal
FROM orders o JOIN order_items oi
    ON o.order_id = oi.order_item_order_id
    JOIN products p
    ON p.product_id = oi.order_item_product_id
LIMIT 10

  • If we have to apply additional filters, it is recommended to use WHERE clause. ON clause should only have join conditions.

  • We can have non-equal join conditions as well, but they are not used that often.

  • Here are some of the examples for INNER JOIN:

    • Get order id, date, status, and item revenue for all order items.
    • Get order id, date, status, and item revenue for all order items for all orders where order status is either COMPLETE or CLOSED.
    • Get order id, date, status, and item revenue for all order items for all orders where order status is either COMPLETE or CLOSED for the orders that are placed in the month of January 2014.

Link to video

Joining Tables – Inner

Description Paragraph

Explanation for the video

Put a place holder for the video here with text so that I can replace as part of the automation

Key Concepts Explanation

Description of the key concepts. Provide key concepts as subheadings and explain them with inline code examples. Multi-line code examples should be highlighted using code highlighter.

Key Concept 1

Description of the key concept 1 with inline code examples
Multi line code examples (if any) should be highlighted using code highlighter

Key Concept 2

Description of the key concept 2 with inline code examples
Multi line code examples (if any) should be highlighted using code highlighter

Hands-On Tasks

Description of the hands-on tasks. Provide a list of tasks that the reader can perform to apply the concepts discussed in the article.

  1. Task 1
  2. Task 2

Conclusion

Summary of the main points discussed in the article. Encourage the reader to practice or engage with the community for further learning.

Joining Tables – Inner

Watch the video tutorial here