Verifying attribute data after join in PyQGIS

Question

I have two attribute tables in qgis:

1. One table has around 20 million records
2. The second table has around 40k records.

I make a join (under Layer-Properties->Joins) in 2nd table and get some data from table 1 and put that into table 2 based on a single Id column. Now I want to verify the data that it is correctly joined but when i put an id into feature search it gets hang as table 1 is very large.

Is there any efficient way available to deal with such a large data set?

I have tried to write a script to verify data but still after running the whole day it is unable to produce any result still running and my QGIS project seems hang:

layerUpdateName = "Extra" #Name of the layer whose data want to update
layerUpdateColumn = "toid" #Column name to update

layerGetName = "Data_Tbl" #Name of the layer from data will be get
layerGetColumn = "TOID" #Cloum name from where value will be taken

dataMatchColumnName = "LOTID" 

#getting the desired layer
if QgsProject.instance().mapLayersByName(layerUpdateName):
    layerUpdate = QgsProject.instance().mapLayersByName(layerUpdateName)[0]; 

if QgsProject.instance().mapLayersByName(layerGetName):
    layerGet = QgsProject.instance().mapLayersByName(layerGetName)[0];
a = 0

for lg in layerGet.getFeatures():
    a += 1
    for lu in layerUpdate.getFeatures():    
        if lu[layerUpdateColumn] == lg[layerGetColumn]:
            print (lu[layerUpdateColumn])
            break;

Answer 1

How about filtering the joined layer for null values? Right click the joined layer and choose filter. QGIS will ask you to create a virtual layer to filter since it is a join-layer. Then you can filter the virtual layer with:

"nameOfColumnThatShouldHaveData" IS NOT NULL

I created a small test data set where I joined the column "data" from the layer "table" onto the point layer "joinedLayer". See the images below for the process.

Right click and filter the joined layer

Press yes to create a virtual layer

Right click the new, virtual layer and press filter again

Filter out null values in the columns added by the join ("table_data" in my case, since it was prefixed in the join process)

And here is the result tables. As you can see, the virtual layer has less features than the joinedLayer, since the NULL values are filtered out. You can also see the differing feature count in the layer list.

You should get the 40 000 rows that has the new column with data in it. You can right click the virtual layer and select "Show feature count" to make sure it is 40 000.

Not sure if this is impacted by the large number of rows, but the process works for a small dataset.

This is not a pyQGIS answer of course, but I got the impression in the comment discussion that pyQGIS was not a requirement.

Another caveat is that if the input join data contains null values, you will filter out those as well of course, thus filtering out rows that were actually joined. You could string together a compound filter to check all joined columns if some have data and some have null like so:

("column1" is not null) AND ("column2" is not null) ...

... and so on.