How to Join First Row in SQL?

SQL(Structured Query Language) is a powerful tool that is used to manage and manipulate data in a database. Join is a very important tool in SQL that combines multiple tables based on related columns between them. Joining the first row of tables in SQL is a very useful technique when you need to fetch additional information from the tables. In this article, we see the different approaches with their syntax example and their explanation with output.

Examples of Joining the First Row in SQL

1. Using Subquery With LIMIT

Using this approach you can limit how many rows you want to fetch from tables.

Syntax:

SELECT *
FROM table1 t1
JOIN (
    SELECT *
    FROM table2
    ORDER BY column_name
    LIMIT num_of_rows
) AS t2 ON t1.common_column = t2.common_column;

Here,

• JOIN: Combines rows from two or more tables based on a related column between them.
• AS: Renames a column or table with an alias to improve readability.
• SELECT: Retrieves data from one or more tables or expressions.
• ORDER BY: Sorts the result set based on specified columns or expressions.
• LIMIT: Constrains the number of rows returned in the result set.

Example: Querying Customer Details with Earliest Order Date

Creating Customer table:

CREATE table customers(customer_id int, customer_name varchar2, email varchar2);
-- Insert values into the customers table
INSERT INTO customers VALUES (1, 'John Doe', 'john.doe@example.com'); 
INSERT INTO customers VALUES (2, 'Jane Smith', 'jane.smith@example.com');

Creating Orders table:

CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT, order_date DATE ); 
 -- Insert values into the orders table
INSERT INTO orders VALUES (101, 1, '2024-01-15');

In this example, we are fetching the details of customer from the table customer and order. In this example, we use nested query and LIMIT which constraints the number of rows returned in the list.

SELECT *
FROM customers c
JOIN (
    SELECT *
    FROM orders
    ORDER BY order_date ASC
    LIMIT 1
) AS o ON c.customer_id = o.customer_id;

Output:

In this output, we fetch the details of one customer from the table customer and order. In output, it returns the column customer_id, customer_name, email, order_id, order_date.

2. Using Subquery With TOP

In this approach, we fetch only top most row from the corresponding table.

Syntax:

SELECT *
FROM table1 t1
JOIN (
    SELECT TOP 1 *
    FROM table2
    ORDER BY column_name
) AS t2 ON t1.common_column = t2.common_column;

Here,

• SELECT: Specifies columns to retrieve data from in tables or subqueries.
• FROM: Identifies the table(s) from which data will be retrieved.
• JOIN: Combines rows from different tables based on a related column.
• TOP 1: Selects only the first row from a result set based on specified criteria.
• ORDER BY: Arrange rows in a specified order, typically ascending or descending.
• AS: Provides an alias for a table or column to simplify referencing in the query.
• ON: Defines the condition for joining tables based on matching column values.

Example: Fetching Earliest Order for Each Customer

In this example, we are fetching the details of customer from the table customer and order. In this example, we use nested query and TOP which Selects only the first row from a result set based on specified criteria.

SELECT *
FROM customer c
JOIN (
    SELECT TOP 1 *
    FROM order
    ORDER BY order_date ASC
) AS o ON c.customer_id = o.customer_id;

Output:

In this output, we fetch the details of one customer from the table customer and order. In output, it returns the column customer_id, customer_name, email, order_id, order_date.

3. Using Subquery With FETCH FIRST

Using this approach we fetch first number of rows of corresponding tables.

Syntax:

 SELECT *
FROM table1 t1
JOIN (
    SELECT *
    FROM table2
    ORDER BY column_name
    FETCH FIRST 1 ROW ONLY
) AS t2 ON t1.common_column = t2.common_column;

Here,

• SELECT: Retrieves all columns from specified tables or subqueries.
• FROM: Specifies the source table(s) or subquery from which data will be retrieved.
• JOIN: Combines rows from different tables based on a related column or condition.
• FETCH FIRST 1 ROW ONLY: Limits the number of rows retrieved to only the first row, according to the specified ordering.
• ORDER BY: Specifies the column(s) by which the result set should be sorted, often in ascending or descending order.
• AS: Assigns an alias to a table or subquery for easier reference in the query.
• ON: Specifies the condition for joining tables, typically by matching values in specified columns.

Example: Retrieving Customer Details with Earliest Order Using FETCH FIRST

In this example, we are fetching the details of customer from the table customer and order. In this example, we use nested query and FETCH FIRST 1 ROW ONLY which Limits the number of rows retrieved to only the first row, according to the specified ordering..

SELECT *
FROM customer c
JOIN (
    SELECT *
    FROM order
    ORDER BY order_date ASC
     FETCH FIRST 1 ROW ONLY
) AS o ON c.customer_id = o.customer_id;

Output:

In this output, we fetch the details of one customer from the table customer and order. In output, it returns the column customer_id, customer_name, email, order_id, order_date.

Conclusion

In conclusion, we explored three simple ways to join to the first row in SQL. In MySQL, we used a subquery with ‘LIMIT‘ to fetch the first row from the related table. In addition, for MySQL, we employed ‘TOP‘ in a subquery to achieve the same result. Lastly, in Oracle, we utilized ‘FETCH FIRST’ within a subquery to select the first row. These approaches offer straightforward methods to retrieve specific data from related tables, enhancing our ability to fetch information efficiently in SQL queries.