Display tables data one after one in MySQL?

Question

actually I have two tables ,i.e table 1 and table 2 . I need to display data from both tables . if I use

SELECT * FROM table 1 UNION SELECT * FROM table 2

Now I am getting data randomly from both tables

BUT I need first display table1 data after display table2 data

Please edit clarifications into your question, not comments. But it is not clear what you are trying to say in your comments--please write longer sentences. Please read & act on [mcve]. PS You must add a column for the table to each, union all, order by that column in the second select (which applies to the union), and select the original columns. Also this is surely a faq, please google many clear, concise, specific statements of you question & read many hits before posting. — philipxy, Aug 29 '17 at 07:07
Possible duplicate of [SQL Server UNION - What is the default ORDER BY Behaviour](https://stackoverflow.com/questions/421049/sql-server-union-what-is-the-default-order-by-behaviour) — philipxy, Aug 29 '17 at 07:23

score 1 · Answer 1 · answered Aug 28 '17 at 08:16

In practice, this will do what you want (assuming the tables have the same columns):

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

It is actually safer to use an explicit order by:

SELECT . . .
FROM (SELECT t1.*, 1 as which FROM table1 t1
      UNION ALL
      SELECT t2.*, 2 as which FROM table2 t2
     ) t
ORDER BY which;

SQL tables represent unordered sets. There is no ANSI requirement that UNION ALL return values from the first subquery before the second. In practice that works in MySQL.

score 1 · Answer 2 · answered Aug 28 '17 at 08:17

1

One way could be adding a column in the both queries which would indicate the source from where it came.

SELECT 
*
FROM 
(
   SELECT *,'T1' AS source FROM table1 
    UNION 
   SELECT *,'T2' FROM table2
) AS t 
ORDER BY t.source ASC;

See Demo

answered Aug 28 '17 at 08:17

1000111

13,169
2
28
37

Select job_id,company_name,job_description,job_location, job_title,maxAnnual_ctc,max_exp_required,minAnnual_ctc,min_exp_required,mandatory_skill1,mandatory_skill2,mandatory_skill3,TQT,suggest_tqt1,suggest_tqt2,suggest_tqt3,Null as jobpost_url,Null as source,added_date from jobs_posted – Niranjan Kumar Chowdam Aug 28 '17 at 10:47
UNION Select job_id, company_name,job_description,job_location,job_title,Null as maxAnnual_ctc, Null as max_exp_required,Null as minAnnual_ctc,Null as min_exp_required,mandatory_skill1,Null as mandatory_skill2,Null as mandatory_skill3,Null as TQT,Null as suggest_tqt1,Null as suggest_tqt2,Null as suggest_tqt3,jobpost_url,source,creted_data_time as added_date FROM indeed_jobcrawl ORDER BY added_date desc LIMIT 10 – Niranjan Kumar Chowdam Aug 28 '17 at 10:48
ADDED ABOVE TWO QUERYS IN A SINGLE QUERY – Niranjan Kumar Chowdam Aug 28 '17 at 10:49
In First query table is jobs_posted i have added_date column , based on that date display data – Niranjan Kumar Chowdam Aug 28 '17 at 10:51

Display tables data one after one in MySQL?

2 Answers2