# SQL Query Concatenation **URL:** \ **Category:** Factual Questions\ **Created:** [December 29, 2005, 3:26pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578 "2005-12-29T15:26:23Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![FlyingCowOfDoom](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/flyingcowofdoom/32/21776_2.png) [@FlyingCowOfDoom](https://boards.straightdope.com/u/FlyingCowOfDoom)\ **Post date:** [December 29, 2005, 3:26pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/1 "2005-12-29T15:26:23Z")
I have two SQL SELECT statements. As an example, say they are “SELECT name FROM people” and “SELECT address FROM billing”. I want to return a result set that is all the results from the first SELECT followed by all the results from the second SELECT. Is there an easy way to do this using only SQL? TIA! –FCOD ---
**Author:** ![Ponster](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ponster/32/17081_2.png) [@Ponster](https://boards.straightdope.com/u/Ponster)\ **Post date:** [December 29, 2005, 3:38pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/2 "2005-12-29T15:38:12Z")
SELECT people.name, billing.address from name, billing. you’ll probably need a where clause at the end though to make sure that you match up the correct records. Is there data that is present in both tables? ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [December 29, 2005, 3:41pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/3 "2005-12-29T15:41:16Z")
This sounds a bit like a union statement situation. Typical syntax is like this: select name from people union select address from billing This will give you one result set with names and addresses all in the same column, called ‘name’. The order might be hard to predict without a specific order by clause, and as is it might delete duplicate results. (Try ‘union all’ to preserve duplicates.) Hope this helps. What is your SQL data source? ---
**Author:** ![FlyingCowOfDoom](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/flyingcowofdoom/32/21776_2.png) [@FlyingCowOfDoom](https://boards.straightdope.com/u/FlyingCowOfDoom)\ **Post date:** [December 29, 2005, 3:56pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/4 "2005-12-29T15:56:29Z")
Well, I can’t expect helpful answers when I don’t ask the question correctly. The two SELECT statements are the same except for different WHERE clauses. Here are new example statements: “SELECT \* FROM people WHERE type=1” “SELECT \* FROM people WHERE type=2” Actually, you were helpful. UNION is exactly what I was looking for. Thank you! –FCOD ---
**Author:** ![ratatoskK](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ratatoskk/32/2987_2.png) [@ratatoskK](https://boards.straightdope.com/u/ratatoskK)\ **Post date:** [December 29, 2005, 4:14pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/5 "2005-12-29T16:14:36Z")
select 1, A from B union select 2, X from Y order by 1 ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [December 29, 2005, 4:38pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/6 "2005-12-29T16:38:03Z")
> [@FlyingCowOfDoom](#): > > Well, I can’t expect helpful answers when I don’t ask the question correctly. The two SELECT statements are the same except for different WHERE clauses. Here are new example statements: > > “SELECT \* FROM people WHERE type=1” > > “SELECT \* FROM people WHERE type=2” > > Actually, you were helpful. UNION is exactly what I was looking for. Thank you! > > –FCOD If the only difference is different where clauses, then I think it is better to try to form a where clause that will encompass both cases. Either of these would work, I think: “SELECT \* FROM people WHERE type=1 or type = 2” “SELECT \* FROM people WHERE type in (1, 2)” UNION is meant for situations where the underlying tables are different, mostly. Still, if it works it works. 🙂 ---
**Author:** ![hajario](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/hajario/32/171_2.png) [@hajario](https://boards.straightdope.com/u/hajario)\ **Post date:** [December 29, 2005, 5:00pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/7 "2005-12-29T17:00:58Z")
If you SQL geeks wouldn’t mind, I have a question over [here.](http://boards.straightdope.com/sdmb/showthread.php?t=351622) ---
**Author:** ![FlyingCowOfDoom](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/flyingcowofdoom/32/21776_2.png) [@FlyingCowOfDoom](https://boards.straightdope.com/u/FlyingCowOfDoom)\ **Post date:** [December 29, 2005, 6:32pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/8 "2005-12-29T18:32:58Z")
> [@chrisk](#): > > If the only difference is different where clauses, then I think it is better to try to form a where clause that will encompass both cases. Either of these would work, I think: > “SELECT \* FROM people WHERE type=1 or type = 2” > > “SELECT \* FROM people WHERE type in (1, 2)” > > UNION is meant for situations where the underlying tables are different, mostly. Still, if it works it works. 🙂 That would work if I didn’t need each query’s results to be sorted differently and then concatenated. Since I don’t want the results to be mixed together but one after the other, I think UNION is the only way to go. I’m pretty good at SQL, but I have never used the UNION statement before. –FCOD ---
**Author:** ![Manduck](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/manduck/32/256_2.png) [@Manduck](https://boards.straightdope.com/u/Manduck)\ **Post date:** [December 29, 2005, 6:54pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/9 "2005-12-29T18:54:14Z")
> [@FlyingCowOfDoom](#): > > That would work if I didn’t need each query’s results to be sorted differently and then concatenated. Since I don’t want the results to be mixed together but one after the other, I think UNION is the only way to go. I’m pretty good at SQL, but I have never used the UNION statement before. > > –FCOD I don’t think the UNION clause guarantees the order in which rows are returned. Better is SELECT \* FROM people WHERE type in (1,2) ORDER BY type ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [December 29, 2005, 7:07pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/10 "2005-12-29T19:07:49Z")
> [@FlyingCowOfDoom](#): > > That would work if I didn’t need each query’s results to be sorted differently and then concatenated. Since I don’t want the results to be mixed together but one after the other, I think UNION is the only way to go. I’m pretty good at SQL, but I have never used the UNION statement before. > > –FCOD If you really need them to be sorted according to different rules, then yes, you may need the ‘union’ operator for that. If the lists can be sorted according to the same criteria amongst themselves, but listed seperately, then it’s easy to use the combined where clause and say: order by type, address, phonenumber or whatever. Notice that the union-select statement also has at most a single order by clause, at the end, which uses column numbers or column names as defined in the first select list. You _cannot_ say something like this: SELECT name, address, phonenumber, type FROM people WHERE type=1 order by phonenumber UNION SELECT name, address, phonenumber, type FROM people WHERE type=2 order by address The first ‘order by’ clause will generate a syntax error IIRC. If you take it out, the second order by clause will affect the entire result set. _however_, you can do tricky stuff like this: SELECT name, address, phonenumber, type, ‘1|’ + phonenumber sortfield FROM people WHERE type=1 UNION SELECT name, address, phonenumber, type, ‘2|’ + address sortfield FROM people WHERE type=2 order by sortfield Which should get the results of: type 1 people sorted by their phone number, then type 2 people sorted by their address. There will be the extra ‘sortfield’ column in your result set, but you shouldn’t need to use that or display it to your user. Does that make any sense?? ---
**Author:** ![FlyingCowOfDoom](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/flyingcowofdoom/32/21776_2.png) [@FlyingCowOfDoom](https://boards.straightdope.com/u/FlyingCowOfDoom)\ **Post date:** [December 29, 2005, 7:37pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/11 "2005-12-29T19:37:21Z")
Here’s what I did: I have one table of products. Each product, among other fields, has a color field. There are a lot of distinct values in this field, i.e. “Amber”, “Amber Violet”, “Black”, “Shiny Black” and so on. My queries are as follows: SELECT \* FROM product WHERE color = ‘Amber’; SELECT \* FROM product WHERE color RLIKE ‘Amber’ AND color != ‘Amber’; As you can see, I want to show all the results from the “exact” search first, followed by the results of the “similar” search. To expand: (SELECT \* FROM product WHERE color = ‘Amber’ ORDER BY name) UNION (SELECT \* FROM product WHERE color RLIKE ‘Amber’ AND color != ‘Amber’ ORDER BY name) LIMIT 20; This is a simplified version of what I am going to use. The actual statements involve joining 5 tables as well, but you get the idea. Because I want to sort the two result sets individually and then LIMIT them as one, I think UNION is my only option. > [@](#): > > The first ‘order by’ clause will generate a syntax error IIRC. If you take it out, the second order by clause will affect the entire result set. **chrisk** : You remember correctly. However, placing the two SELECT statements within parenthesees will correct the error. –FCOD ---
**Author:** ![FlyingCowOfDoom](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/flyingcowofdoom/32/21776_2.png) [@FlyingCowOfDoom](https://boards.straightdope.com/u/FlyingCowOfDoom)\ **Post date:** [December 29, 2005, 7:38pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/12 "2005-12-29T19:38:59Z")
BTW I’m using MySQL. If you use another DB engine YMMV. –FCOD ---
**Author:** ![Manduck](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/manduck/32/256_2.png) [@Manduck](https://boards.straightdope.com/u/Manduck)\ **Post date:** [December 29, 2005, 8:20pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/13 "2005-12-29T20:20:19Z")
I think you can get what you want by saying SELECT \* FROM product WHERE color RLIKE ‘Amber’ ORDER BY color, name That should put ‘Amber’ ahead of ‘Amber violet’, ‘Amber pink’, etc. (if RLIKE works the way I’m guessing - I’m not familiar with that operator but I assume it’s like Oracles LIKE ‘Amber%’). ---
**Author:** ![Manduck](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/manduck/32/256_2.png) [@Manduck](https://boards.straightdope.com/u/Manduck)\ **Post date:** [December 29, 2005, 8:21pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/14 "2005-12-29T20:21:22Z")
Whoops, no, I lied, ignore my previous post - I forgot about the different sorting in the two halves. ---
**Author:** ![FlyingCowOfDoom](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/flyingcowofdoom/32/21776_2.png) [@FlyingCowOfDoom](https://boards.straightdope.com/u/FlyingCowOfDoom)\ **Post date:** [December 29, 2005, 8:38pm UTC](https://boards.straightdope.com/t/sql-query-concatenation/337578/15 "2005-12-29T20:38:32Z")
FYI - WHERE x RLIKE _pattern_ - Matches x against a regular expression _pattern_ –FCOD