Select from multiple tables without a join

Question

What is the easiest way to select data from two tables and rather than join them  have them appear as separate rows  Both tables have similar or matching fields and I want to run some aggregate function on them such as avg all the rows that occurred in the same month  from both tables   for example I have two tables  one that is shows transactions from one system and another with transactions from a different system  Is there a way to grab all the transactions from both tables as separate rows  if table 1 had twenty records and table 2 have thirty records  I d like there to be 50 rows on the return

User · Answer

You could try something like this  SELECT     FROM       SELECT f1 f2 f3 FROM table1     UNION     SELECT f1 f2 f3 FROM table2   WHERE

User · Answer

You should try this   SELECT t1   t2   FROM t1 t2

User · Answer

Union will fetch data by row not column So If your are like me who is looking for fetching column  data from two different table with no relation and without join  In my case I am fetching state name and country name by id  Instead of writing two query you can do this way      select          select s state name from state s where s state id 3      statename          select c description from country c where c id 5      countryname    from dual       where dual is a dummy table with single column--anything just require table to view

User · Answer

You could try this notattion   SELECT   from table1 table2    More complicated one    SELECT table1 field1 table1 field2  table2 field3 table2 field8 from table1 table2 where table1 field2   something and table2 field3   somethingelse

User · Answer

If your question was this -- Select ename  dname FROM emp  dept without using joins    Then  I would do this     SELECT ename   SELECT dname  FROM dept WHERE dept deptno emp deptno dname FROM EMP   Output   ENAME      DNAME ---------- -------------- SMITH      RESEARCH ALLEN      SALES WARD       SALES JONES      RESEARCH MARTIN     SALES BLAKE      SALES CLARK      ACCOUNTING SCOTT      RESEARCH KING       ACCOUNTING TURNER     SALES ADAMS      RESEARCH  ENAME      DNAME ---------- -------------- JAMES      SALES FORD       RESEARCH MILLER     ACCOUNTING  14 rows selected

User · Answer

In this case we are assuming that we have two tables  SMPPMsgLogand SMSService with common column serviceid   SELECT sp SMS ss CMD  FROM vas SMPPMsgLog AS sp vas SMSService AS ss  WHERE sp serviceid 5431  AND ss ServiceID   5431  AND Receiver   232700000   AND date TimeStamp   lt   2013-08-07   AND date TimeStamp   gt   2013-08-06   G

User · Answer

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

User · Answer

select  test    select name from employee where id 1  as name   select name from address where id 2  as address

[mysql] Select from multiple tables without a join?

Examples related to mysql