![]() ![]() The only limiting factor for MySQL appeared to be the SQL string length this bit of code routinely sent several thousand “union strings” using this approach. I did it this way as it was easiest for that bit of code to generate. (select ‘foo’ as bogocol union select ‘bar’ union select ‘baz’ union … union ‘endstr’ ) as x I don’t know about Oracle, but I’ve occasionally done this in MySQL to get a big list of program-generated stuff into a “tabular form” in something like this Author has 4.9k answers and 13.5m answer views.Greg Kemnitz, Postgres internals, embedded device db internals, MySQL user-level In the following example we are retrieving all the values using the UNION ALL.I would think the limit would be on system memory resources rather than the query itself. Syntax of UNION ALL mysql> SELECT column_name FROM table_a We use the UNION ALL when we want to get all the values and not the distinct values. In the employeeaddress table we can see repetition of city name like Bangalore, Chennai and Kolkata, state name like KA, TN and WB.īut using the UNION in the query returns the city and state name only once as it is selecting only the unique i.e., distinct values. mysql>SELECT city, state, country FROM employeeaddress In the following example we are listing all the city, state and country name that are distinct in both the employeeadress and office tables. When using UNION in our query we will get distinct values from the tables. ![]() | 6 | e05 | PRIMARY | Apartment 10 | Fast Food Block | Chennai | TN | INDIA | 600000 | | 5 | e04 | PRIMARY | Fruits Apartment | Fresh Block | Bangalore | KA | INDIA | 560000 | | 4 | e02 | PRIMARY | Pizza Street | Foodies Block | Kolkata | WB | INDIA | 700000 | | 3 | e03 | SECONDARY | House #301 ABC Colony | Jupiter Block | Kolkata | WB | INDIA | 700000 | | 2 | e03 | PRIMARY | Super Housing Complex Block 22 | Major Street | Chennai | TN | INDIA | 600000 | | 1 | e01 | PRIMARY | #12 street | Action block | Bangalore | KA | INDIA | 560000 | | id | employeeid | addresstype | addressline1 | addressline2 | city | state | country | pincode | Table: employeeaddress mysql> SELECT * FROM employeeaddress | o03 | Game | #12 Super Street | Master Block | Chennai | TN | INDIA | 600000 | | o02 | Apps | #321 Super Street | Action Road | Kolkata | WB | INDIA | 700000 | | o01 | Web Projects | #123 Awesome Street | Super Main | Bangalore | KA | INDIA | 560000 | | officeid | officename | addressline1 | addressline2 | city | state | country | pincode | Table: office mysql> SELECT * FROM office In this tutorial we will be using the office and employeeaddress table. Where, column_name must be of same type and same order in both the SELECT statements. Syntax of UNION mysql> SELECT column_name FROM table_a
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |