An SQL join clause combines records from two or more tables or views.

learn more… | top users | synonyms

0
votes
0answers
44 views

MySQL - query join returning zero recs from table with data

I've provided a file that has the table structure and some sample data that I'm working with. I don't understand why I'm getting nulls from a joined table, where there is data. Here's my query ...
0
votes
0answers
58 views

Equivalent of old style join with new style

Can someone please help me write the following join expression in new style: Select tb1.col1,tb2.col2 from table1 tb1, table2 tb2 where tb1.col1>=tb2.col2 I know that the equivalent of ...
0
votes
2answers
190 views

Which is better? A join or a SELECT IN? [closed]

I have a set of ids that I extract using a join of 2 tables. Now I need some information from 2 other tables. What would normally be the best approach? So a SELECT IN using the set of ids I already ...
1
vote
2answers
2k views

SQL Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update ...
0
votes
0answers
122 views

Alternatives for subquery

There are three tables: product_hits, products, and product_subcategory_map. I want to count the number of product_hits and for each product. There could be more than one subcategory in the ...
0
votes
1answer
55 views

MySQL: join query

I've got the following two tables: mysql> DESC domains; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
0
votes
0answers
120 views

Select Foo object without any specific Bar events

Consider two tables: Foo: id INT, name VARCHAR Bar: id INT, foo_id INT REFERENCES Foo(id), event_type VARCHAR DEFAULT NULL, event_duration INT DEFAULT NULL Each Foo item can have ...
3
votes
2answers
179 views

cross database way to force join strategies

[how to enforce the query to use certain join algorithm] Knowing that there are several joining algorithms [Nested Loop; Sort Merge; Hash Join], when implementing a query , is there any technique to ...
3
votes
1answer
95 views

postgres filtering select based on combination of joins

I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship) Please consider the following example. CREATE TABLE sample_container ( id serial ...
11
votes
1answer
260 views

Why does subquery use parallelism and join doesn't?

Why does SQL server use parallelism when running this query which uses a subquery but it doesn't when using a join? The join version runs in serial and takes around 30 times longer to complete. Join ...
0
votes
1answer
62 views

Would it be better to do a sepparate query instead of a left JOIN?

Only a couple of rows is returned when not using LEFT since there isn't always data in the associated table - recipe_preparation. This is one of a couple of tables with the same "issue". SELECT r.id, ...
3
votes
1answer
426 views

Can I provide a default for a left outer join?

Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join SELECT * FROM a LEFT OUTER JOIN b ON a.a1 = b.b1 Then b1 and b2 will be NULL where a value of ...
0
votes
0answers
93 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...
0
votes
2answers
72 views

Is my JOIN syntax not valid? And have I understood JOIN correctly?

I've been reading up a bit regarding JOINs. And i just noticed that I've been writing the syntax a little bit different. Although I don't think there's any problem with it, I would like to hear your ...
1
vote
1answer
97 views

How to perform a multiplication by a different select query

Hope my question is accepted here and I haven't made a mess out of renaming the tables and columns so you can understand what I need! :) Query A: DECLARE @var VARCHAR(7) SET @var = 'xtpo' SELECT ...
2
votes
5answers
11k views

Full Outer Join in MS Access

I have two staff lists: List A: StaffID Supervisor ==================== 0001234 NULL 0001235 0001234 0001237 0001234 0001239 0001237 and List B: StaffID Supervisor ...
1
vote
1answer
936 views

UPDATE a column by COUNT of another table

It might be a naive question, but what is the difference of these two queries and which is preferred? UPDATE table1, (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B SET table1.Freq = ...
0
votes
1answer
73 views

Get all items from list table which don't have a successful log entry for 'sent' in log table

How might one retrieve items that when joined either don't have a specific field (sent), or if they do have that field (sent) then they have an additional field (failure_reason) as well for every sent ...
2
votes
1answer
75 views

Clustered Index / Index Oriented Table performance in join, worse than nonclustered?

Can a clustered index (or IOT in Oracle) be detrimental, when to be used on a very "broad" table, but only few columns are used? In this case, the "Product" table is used only like a junction table ...
1
vote
2answers
425 views

Full outer join does not work for two small tables, error 1064

folks. I am currently exploring the capabilities of MySQL while doing simple exercises with diverse queries. I was reading the following article that explains how table JOIN works: ...
1
vote
1answer
43 views

Estimating a Size of Joining a Relation with itself

I'm studying size estimation of logical query plans in order to select a physical query plan. I was wondering what is the size of joining (natural join) a relation to itself? e.g R(a,b) JOIN R(a,b)
1
vote
1answer
58 views

Formulating a Join Query for PostqreSQL

I have a quick question, if someone can help, I would be grateful I have the following tables User, Video, User_video user has userdetails (user_id, name, email) video has videoDetails ...
1
vote
1answer
518 views

Using OFFSET in a large table by a simple sub-query

The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as SELECT * FROM table INNER JOIN ( SELECT id FROM ...
1
vote
0answers
54 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
1
vote
0answers
83 views

Relation between lossy decomposition and Natural join?

I am confused with the lossy decomposition and natural join. Natural join(with common attribute): only respective common attribute for the resultant table is formed. Lossy: Natural join on tables ...
0
votes
2answers
142 views

How can the LEFT JOIN return more results than the unjoined query?

Consider the following queries: mysql> SELECT count(*) FROM list l WHERE l.source='blink'; +----------+ | count(*) | +----------+ | 3372 | +----------+ 1 row in set (0.00 sec) mysql> ...
0
votes
1answer
391 views

Calling another function inside a postgres function

I have a function A and function B. Function A Name is f_a() and the code is: Select id,address from A; Function B Name is f_b() code is: Select C.address,B.name From (Select id,name from B, ...
1
vote
2answers
89 views

Duplicates while doing multiple joins

I have 4 MySQL tables 1. companies - {id*, company} 2. employees - {id*, fname, lname, id_company*} 3. technologies - {id*, technology} 4. company_technologies - {id_technology*, id_company*} Field ...
0
votes
1answer
95 views

Mysql list data from left join query

I have 2 table and i want to pull data from both of them. This is sample for my table structure: Table tbl_a = id, time, note, customer Table tbl_b = id, time, name, surname, note This is my mysql ...
0
votes
1answer
79 views

Join and Count based on Specific User ID in SQL

I have the following tables, I inserted some dummy data in it for demonstration purposes. I would like to join these two tables in a way that based on the user ID, I can get the count of similar ...
1
vote
1answer
143 views

INSERT INTO selected columns creates new column from JOIN [closed]

I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the ...
0
votes
1answer
79 views

Merge two tables on partly matching column

I have below 2 tables. old_name old_value Tom1 100 Kate1 80 Jim1 70 new_name new_value Tom2 70 Kate2 100 Jim2 80 I want to make a new table look like ...
0
votes
1answer
137 views

Is it better to put filters on JOIN clauses instead of WHERE clauses

MySQL 5.5.8: Question 1: Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables ...
0
votes
1answer
92 views

Update , Select and Join using 3 tables

There are 3 tables: Label (Label_ID,Label_Name), Label_ID is Primary Key. Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column Top100 (Prefix,Label) No keys in this table. My ...
4
votes
2answers
2k views

SQL join query to show rows with non-existent rows in one table

I am trying to get some reporting done for employee time records. We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of ...
0
votes
1answer
31 views

Sorting by a field in child table

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract). contractupdate also has DATE field startdate and TINYINT field day. I ...
1
vote
1answer
25 views

Optimised query to select document and possible annotations

I have a database of small documents which users can view and annotate. Their annotations are not public. I'm looking to optimise a single query to read the document and annotations for the current ...
4
votes
2answers
700 views

How can row estimates be improved in order to reduce chances of spills to tempdb

I notice that when there are spill to tempdb events (causing slow queries) that often the row estimates are way off for a particular join. I've seen spill events occur with merge and hash joins and ...
1
vote
1answer
102 views

Query_cache doesn't work with join

I have a simple join query, but for some reason query_cache won't cache it! SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id WHERE ...
1
vote
0answers
87 views

Can't get rid of filesort on joined query sorting on second table

For some reason, this query: EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5; says it ...
2
votes
2answers
2k views

MySQL not using indexes when joining against another table

I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with ...
4
votes
1answer
3k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
0
votes
1answer
41 views

How to create new column with value from other table

I have a table called 'menus': menu_id | sort | page_id | parent_page_id ---------|------|---------|---------------- 1 | 0 | 1 | NULL 1 | 1 | 2 | 1 1 | 2 | ...
0
votes
2answers
667 views

Does using LIMIT improve the performance and is it noticeable?

I want to understand the following. Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by. Letting aside any optimizations to the query itself ...
1
vote
0answers
625 views

multiple child records in a single row by join

I have one master table (teacher) structured like teacherId Name Class 1 Praveen 10 2 John 9 and having a child table (student) structured like studentId ...
1
vote
1answer
144 views

Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome. In short we have left-join query like: select from a left join b on a.key1=b.key1 or a.key1=b.key2 This works VERY slowly, while ...
0
votes
0answers
82 views

Better way to query for object with multi level parts

For example, an object consists of several parts, and each part consists of several sub-parts, and so on. In MySQL, it looks like this: table `object` ----------------------- object_id, object_name ...
1
vote
1answer
97 views

How to prevent Cartesian joins when value can be in either field?

I have a table of articles where the same article may be presented to different audiences with slightly altered text depending on the user's access level. When this occurs, the article will have the ...
0
votes
0answers
308 views

MySql INNER JOIN doesn't use composite index

I have two tables, table A which contains date and name of table B entry and table B which contains its name and type. Table a has a composite index on table_b_name and date. I'm selecting all table A ...
1
vote
3answers
944 views

Conditional Join - Specific Column Select

I have tables like these: Table Employee ( Emp_Id, Full_Name, ...) Table Order ( Submitter_Id, Recipient_Id, ...) Then say that I have conditional join like this: select isnull(Emp.Full_Name, ...