Combine two queries, which include join operations, into a single query-CodePudding

I have "article" table, and "used" table for registration of rentals.

I want to know which articles are free, or in other words, the ones that have never been rented (table article) or the ones that are returned (table used).

I have 2 seperate queries and they work in the way I expected, but I'd want to combine them into a single query.

First query

SELECT      a.article_id, a.mark, a.type, a.description
FROM        article a
INNER JOIN  used u ON u.article_id = a.article_id
WHERE       return_date IS NOT NULL

Second query

SELECT      article_id, mark, type
FROM        article
WHERE       NOT EXISTS
            (SELECT *
            FROM used
            WHERE article.article_id = used.article_id)

The first query returns 25 records, while the second query returns 113 records. The final output should return 138 records.

How can I do it?

Thanks in advance for your help.

CodePudding user response：

This is typically carried out by the UNION ALL operator, that adds up the records of one query to the records of the other. Make sure both the two tables you are making this operation on have the same number of fields and corresponding datatypes.

SELECT      a.article_id, a.mark, a.type
FROM        article a
INNER JOIN  used u ON u.article_id = a.article_id
WHERE       u.return_date IS NOT NULL

UNION ALL

SELECT      article_id, mark, type
FROM        article
WHERE       NOT EXISTS
            (SELECT *
            FROM used
            WHERE article.article_id = used.article_id)

Although it seems you can simplify this whole query using a single LEFT JOIN operation, hence avoiding making two queries out of it.

SELECT a.article_id, a.mark, a.type
FROM   article a
LEFT JOIN  used u 
       ON u.article_id = a.article_id