Select from multiple tables without a join?
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.
Solution 1:
You could try something like this:
SELECT ...
FROM (
SELECT f1,f2,f3 FROM table1
UNION
SELECT f1,f2,f3 FROM table2
)
WHERE ...
Solution 2:
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
Solution 3:
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
Solution 4:
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.
Solution 5:
You should try this
SELECT t1.*,t2.* FROM t1,t2