How do you retrieve data from multiple tables in mysql without join?

The UNION ALL operator may be what you are looking for.

With this operator, you can concatenate the resultsets from multiple queries together, preserving all of the rows from each. Note that a UNION operator [without the ALL keyword] will eliminate any "duplicate" rows which exist in the resultset. The UNION ALL operator preserves all of the rows from each query [and will likely perform better since it doesn't have the overhead of performing the duplicate check and removal operation].

The number of columns and data type of each column must match in each of the queries. If one of the queries has more columns than the other, we sometimes include dummy expressions in the other query to make the columns and datatypes "match". Often, it's helpful to include an expression [an extra column] in the SELECT list of each query that returns a literal, to reveal which of the queries was the "source" of the row.

SELECT 'q1' AS source, a, b, c, d FROM t1 WHERE ...
UNION ALL
SELECT 'q2', t2.fee, t2.fi, t2.fo, 'fum' FROM t2 JOIN t3 ON ...
UNION ALL
SELECT 'q3', '1', '2', buckle, my_shoe FROM t4

You can wrap a query like this in a set of parenthesis, and use it as an inline view [or "derived table", in MySQL lingo], so that you can perform aggregate operations on all of the rows.

SELECT t.a
     , SUM[t.b]
     , AVG[t.c]
  FROM [
         SELECT 'q1' AS source, a, b, c, d FROM t1
          UNION ALL
         SELECT 'q2', t2.fee, t2.fi, t2.fo, 'fum' FROM t2
       ] t
 GROUP BY t.a
 ORDER BY t.a

SELECT Sales.*, Order.*, Department.*, Member.*
FROM Sales
    JOIN Order
        ON Order.aID = Sales.aID
    JOIN Department
        ON Department.cID = Order.cID
    JOIN Member
        ON Member.dID = Sales.dID
WHERE DATE[Department.date]=date[now[]] 

SELECT mem_name AS name from members
UNION       
SELECT team_name AS name from teams;

SELECT mem_name AS name from members
UNION ALL      
SELECT team_name AS name from teams;

SELECT Products_Namn, Member_Namn, Member_Age, Shop_Namn FROM Products

JOIN Member ON Member_Id = Member_Member_Id

JOIN Products_has_Shop ON Products_Products_Id = Products_Id

JOIN Shop ON Shop_Id = Shop_Shop_Id;

About Us

Querying Multiple Tables without Joins Mar 15, 2021 by Robert Gravelle

Normally, querying a normalized database necessitates joining tables together on one or more common fields. Otherwise, you risk generating a cartesian product. That is a result set whose number of rows equals those in the first table multiplied by the number of rows in the second table. So, if the input contains 1000 persons and 1000 phone numbers, the result consists of 1,000,000 pairs! Not good. Having said that, if you wanted to aggregate data from similar tables that are not directly related, you can do that using the UNION operator. In today's blog, we'll learn some of the finer points on using UNION, along with its close cousin, UNION ALL.

UNION versus UNION ALL

Some people think that Union and Union All are interchangeable. They are not. They differ in that Union removes duplicate rows or records, whereas Union All does not; instead, it just selects all the rows from the tables which meet the conditions of your queries' WHERE criteria and combines them into the results.

Combining Results with UNION

Here's a query that combines the results of two SELECT queries using the Sakila Sample Database:

Let's say that you wanted to find the names of all the actors and customers whose first name is the same as the first name of the actor with ID 8, but without returning the actor 8's details. Although there is more than one way to achieve this, one solution is to employ UNION ALL [UNION would work as well, since there are no duplicates between each result set]. Here is the query that does the job, along with the results, in the Navicat Premium database development and admin client:

In the above query, the top SELECT fetches customers whose first name matches that of the actor with the actor_id of 8, while the bottom query fetches actors with the same first name as their fellow actor with ID 8.

If you are a Navicat user, you are already aware that it's editor is one of the best. It provides syntax highlighting, reusable code snippets, as well as auto-complete. When you start to type a word, a popup list appears with suggestions for everything from schemas, tables/views, columns, as well as stored procedures and functions. Here is the UNION operator:

A few final words about UNION and UNION ALL. It is crucial that all queries return the same number of columns or you'll get an error similar to the following:

That being said, the column types do not have to match across all SELECT statements.

And finally, keep in mind when employing UNION or UNION ALL that the column names for the result set are determined by the first SELECT.

Conclusion

The UNION and UNION ALL operators are the perfect tool for aggregating data from similar tables that are not directly related.

If you'd like to give Navicat Premium a try, you can test drive it for 14 days completely free of charge for evaluation purposes!

• Key Topics

• • Navicat 16 Highlights
  • Collaboration
  • What is Navicat for MongoDB
  • What is Navicat Data Modeler
  • Discover Navicat Monitor
  • Top 10 Reasons

• Products

• • Navicat
  • Navicat Collaboration
  • Navicat Charts
  • Navicat Monitor
  • Navicat Data Modeler

• Support

• • Manuals
  • Articles
  • Survey
  • Live Support

• Account

• • Customer Center
  • Subscription Portal
  • Navicat Cloud

• Partners

• • Find Resellers
  • Resellers
  • Technology Partners
  • Sponsorships
  • Navicat Academic Partner Program

• About Us

• • Our Customers
  • Awards
  • Press
  • Blog

• Others

• • Software Maintenance
  • Upgrades
  • Site Licenses
  • Offline Orders
  • Unsubscribe

How do you fetch data from multiple tables in mysql without join?

How can I join multiple tables in SQL without joins?

How can I retrieve data from multiple tables in SQL?

How can I retrieve data from 3 tables in SQL?