Skip to main content

Mysql Union

The UNION set operator is used for combining data from two
tables which have columns with the same datatype.When a UNION is performed the data from both tables will be collected in a single column having the same datatype.

Rules:

  • The number of columns appears in the corresponding SELECT statements must be equal.
  • The columns appear in  the corresponding positions of each SELECT statement must have the same data type or at least convertible data type.
  • You cannot use the union operator on text and image columns.


Notes:

  • Union -- returns with no duplicate rows
  • Union all -- retruns with duplicate rows (No. of rows returned = No. of rows in Query1 + No. of rows in Query 2)


Example:
Suppose you want to combine data from the  student and teacher tables into a single result set, you can UNION operator as the following query:

SELECT studentNumber id, contactLastname name FROM student
UNION
SELECT teacherNumber id,firstname name FROM teacher

Here is the output:
id name
1  Sudhir
2  Amit
3  Gaurav
4  Himanshu

Order by with UNION

 (SELECT studentNumber id,contactLastname name FROM student)
UNION
(SELECT teacherNumber id,firstname name FROM teacher)
ORDER BY name,id

Note: If you place the ORDER BY clause in each SELECT statement,
it will not affect the order of the rows in the final result produced by the UNION operator.
Labels:

Comments

Post a Comment

Popular posts from this blog

Generate XML file in Cakephp

Steps to Generate XML file using CakePHP: Step-1 Enable to parse xml extension in config route.php file.     Router::parseExtensions('xml'); Step-2 Add Request Handler Component to the Controller    var $components = array(‘RequestHandler’); Step-3 Add controller Action For XML Generation in Post Controller     function generateXMLFile()     {         if ($this->RequestHandler->isXml()) { // check request type             $this->layout = 'empty'; // create an empty layout in app/views/layouts/empty.ctp              }        }  Add header code in empty layout <?php header('Content-type: text/xml');?> <?php echo $this->Xml->header(); ?> <?php echo $content_for_layout; ?> Step-4 Set up View To generate XML Create xml folder inside Posts vi...
53 comments
Read more

Simple JavaScript Object and Class Examples

In JavaScript, most things are objects. An object is a collection of related data and/or functionality Namespace: Everything you create in JavaScript is by default global. In JavaScript namespace is Global object . How to create a namespace in JavaScript? Create one global object, and all variables, methods, and functions become properties of that object. Example:  // global namespace var MYAPP = MYAPP || {}; Explaination: Here we first checked whether MYAPP is already defined.If yes, then use the existing MYAPP global object, otherwise create an empty object called MYAPP How to create sub namespaces? // sub namespace MYAPP.event = {}; Class JavaScript is a prototype-based language and contains no class statement.JavaScript classes create simple objects and deal with inheritance. Example: var Person = function () {}; Explaination: Here we define a new class called Person with an empty constructor. Use the class keyword class Calculation {}; A class expr...
25 comments
Read more

How to replace plain URLs with links

Here we will explain how to replace Urls with links from string Using PHP $string ='Rajiv Uttamchandani is an astrophysicist, human rights activist, and entrepreneur. Academy, a nonprofit organization dedicated to providing a robust technology-centered education program for refugee and displaced youth around the world.  CNN Interview - https://www.youtube.com/watch?v=EtTwGke6Jtg   CNN Interview - https://www.youtube.com/watch?v=g7pRTAppsCc&feature=youtu.be'; $string = preg_replace('@(https?://([-\w\.]+)+(:\d+)?(/([\w/_\.%-=#]*(\?\S+)?)?)?)@', '<a href="$1">$1</a>', $string); Using Javascript <script> function linkify(inputText) {     var replacedText, replacePattern1, replacePattern2, replacePattern3;     //URLs starting with http://, https://, or ftp://     replacePattern1 = /(\b(https?|ftp):\/\/[-A-Z0-9+&@#\/%?=~_|!:,.;]*[-A-Z0-9+&@#\/%=~_|])/gim;     replacedText = inputT...
1 comment
Read more